Structured Query Language (SQL) is a standardized programming language that is used to manage relational databases and perform various operations on the data in them. A relational database organizes data into rows and columns, which collectively form a table. SQL can be termed as a language used to talk to a database.
SQL is useful for the following tasks
- To create new databases and tables
- To insert records in a database
- To update records in a database
- To delete records from a database
- To retrieve data from a database
SQL Data Types
In SQL, data types define what kind of data can be stored in a column. Some common data types include:
INT: Integer numbers.
VARCHAR(n): Variable-length text (with a maximum length of n characters).
DATE: Date values (e.g., '2045-05-14').
BOOLEAN: Boolean values (TRUE or FALSE).
Basic SQL commands
- CREATE
The CREATE TABLE statement is used to create a new table in a database.
Syntax
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
);
Example:
CREATE TABLE Students (
StudentID int,
LastName varchar(255),
FirstName varchar(255),
Age int,
Gender varchar(255);
The second query creates a table called Students with the following five columns.
The StudentID & Age columns are of type int and will hold an integer.
The LastName, FirstName and Gender columns are of type varchar and will hold characters, and the maximum length for these fields is 255 characters.
- INSERT INTO
The INSERT INTO statement is used to insert new records in a table.
Syntax
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
INSERT INTO Students (StudentID, FirstName, LastName, Departmentid)
VALUES (101, 'Juliette', 'Warner',1998);
Output:
- SELECT
SQL SELECT statement is used to select rows and columns from a table in
the database.
Syntax:
SELECT column1, column2, ...
FROM table_name;
SELECT * FROM table_name ; returns all records in a table.
- WHERE
Used to filter data based on a specified condition .Often used with comparison operators(>,=,<=,>=,<,=!).
Syntax:
SELECT column_name(s)
FROM table_name
WHERE condition
The query returns the records of employees who have salary greater than 90,000
- GROUP BY
The GROUP BY statement groups rows that have the same values into summary rows, enabling one to run aggregate functions( COUNT() , MAX() , MIN() , SUM() , AVG() ).
Syntax:
SELECT column_name(s)
FROM table_name
WHERE condition
A query calculating the average monthly salary for each department by grouping the employees based on their department.
SELECT department,AVG(monthly_salary)
FROM employees
GROUP BY department ;
Output:
- ORDER BY
The command sorts the results set in either ascending or descending order.
Syntax:
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;
A query sorting the employees from based on their monthly salary in descending order
SELECT *
FROM employees
ORDER BY monthly_salary DESC ;
- HAVING CLAUSE
The HAVING clause is mostly used with aggregate functions. Having clause filters the records from groups based on specified conditions.
Syntax:
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
A query that filters departments having their average salary greater than 70,000
- DELETE
The DELETE statement is used to delete existing records in a table.
Syntax:
DELETE FROM table_name WHERE condition;
The WHERE clause specifies which records should be deleted and if omitted, all records in the table will be deleted.
It is also possible to delete all rows in a table without deleting the table. using DELETE FROM table_name; syntax
The query deletes all entries in the student table
Conclusion
In this introduction, we explored the basics of SQL and how it helps manage and interact with data stored in relational databases.Stay tuned for the next part, where we will dive deeper into intermediate SQL concepts.
Thank you for taking the time to read this article.
Top comments (0)