SQL (Structured Query Language) is a programming language designed for managing and manipulating data in a relational database management system (RDBMS). In this tutorial, we will cover the basics of SQL, including how to create tables, insert and retrieve data, and perform queries.
Creating Tables:
To create a table in SQL, use the CREATE TABLE statement. The statement requires a table name and a list of columns along with their data types.
Example:
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT,
salary FLOAT
);
This statement creates a table called "employees" with columns for ID, name, age, and salary.
Inserting Data:
To insert data into a table, use the INSERT INTO statement. The statement requires the table name and a list of values for each column.
Example:
INSERT INTO employees (id, name, age, salary)
VALUES (1, 'John Doe', 25, 50000.00);
This statement inserts a new row into the "employees" table with the values for ID, name, age, and salary.
Retrieving Data:
To retrieve data from a table, use the SELECT statement. The statement requires the columns you want to retrieve and the table you want to retrieve them from.
Example:
SELECT name, age, salary FROM employees;
This statement retrieves the "name", "age", and "salary" columns from the "employees" table.
Updating Data:
To update data in a table, use the UPDATE statement. The statement requires the table name, the column you want to update, and the new value for that column.
Example:
UPDATE employees SET salary = 60000.00 WHERE id = 1;
This statement updates the salary column in the "employees" table for the row with the ID of 1 to 60000.00.
Deleting Data:
To delete data from a table, use the DELETE statement. The statement requires the table name and the condition for the rows you want to delete.
Example:
DELETE FROM employees WHERE id = 1;
This statement deletes the row from the "employees" table with an ID of 1.
Querying Data:
To query data from a table, use the SELECT statement. The statement requires the columns you want to retrieve and the table you want to retrieve them from. You can also use WHERE clause to specify the condition for rows to be returned.
Example:
SELECT name, age, salary FROM employees WHERE age > 30;
This statement retrieves the "name", "age", and "salary" columns from the "employees" table where age is greater than 30.
SQL JOIN is a clause that allows you to combine data from two or more tables in a relational database. It is used to fetch data from multiple tables based on a related column between them.
There are different types of SQL JOIN:
1. INNER JOIN: returns only the matching rows between two tables based on the join condition.
Syntax:
SELECT table1.column_name, table2.column_name
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
2. LEFT JOIN: returns all rows from the left table and matching rows from the right table based on the join condition. If there are no matching rows in the right table, the result will contain NULL values.
Syntax:
SELECT table1.column_name, table2.column_name
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;
3. RIGHT JOIN: returns all rows from the right table and matching rows from the left table based on the join condition. If there are no matching rows in the left table, the result will contain NULL values.
Syntax:
SELECT table1.column_name, table2.column_name
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;
4. FULL OUTER JOIN: returns all rows from both tables, including the unmatched rows from each table. If there are no matching rows in either table, the result will contain NULL values.
Syntax:
SELECT table1.column_name, table2.column_name
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name;
5. CROSS JOIN: returns the Cartesian product of both tables, which means all possible combinations of rows from both tables. It does not require any join condition.
Syntax:
SELECT table1.column_name, table2.column_name
FROM table1
CROSS JOIN table2;
Note: In the above syntax, you can replace column_name with the actual name of the column(s) you want to select from the table(s).
Here are some examples of SQL functions in action:
1.COUNT function:
SELECT COUNT(*) FROM orders WHERE order_status = 'shipped';
This query returns the number of orders that have a status of 'shipped'.
2. AVG function:
SELECT AVG(order_total) FROM orders WHERE order_status = 'completed';
This query returns the average order total for all completed orders.
3. UPPER function:
SELECT UPPER(customer_name) FROM customers;
This query returns the customer names in all uppercase letters.
4. CONCAT function:
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;
This query concatenates the first name and last name columns in the employees table and returns the result as a new column called full_name.
5. TRIM function:
SELECT TRIM(' Hello, World! ') AS trimmed_string;
This query trims the leading and trailing spaces from the string ' Hello, World! ' and returns the result as trimmed_string.
Comments
Post a Comment