SELECT column_list FROM table-name
[WHERE Clause]
[GROUP BY clause]
[HAVING clause]
[ORDER BY clause];
SELECT name, salary, salary*1.2 AS new_salary FROM employee
WHERE new_salary /salary*1.2 > 30000;
SELECT first_name, last_name, subject
FROM student_details
WHERE subject IN/NOT IN ('Maths', 'Science');

Select * from product p where EXISTS (select * from order_items o where o.product_id = p.product_id)

------------------------------------------------- SELECT user.name, course.name
-------------------------------------------------
FROM `user`
-------------------------------------------------
RIGHT JOIN `course` on user.course = course.id

SELECT name, salary FROM employee ORDER BY 1, 2;
ELECT name, salary
FROM employee
ORDER BY name DESC, salary DESC;
SELECT COUNT (DISTINCT name) FROM employee;
HAVING Group functions (MAX,MIN) cannot be used in WHERE Clause but can be used in HAVING clause.
SELECT dept, SUM (salary)
FROM employee
GROUP BY dept
HAVING SUM (salary) > 25000
INSERT INTO TABLE_NAME
[ (col1, col2, col3,...colN)]
VALUES (value1, value2, value3,...valueN);
INSERT INTO employee (id, name, dept, age, salary location) SELECT emp_id, emp_name, dept, age, salary, location
FROM temp_employee;
Copy data from A to B. Select first then insert
If you are inserting data to all the columns, the above insert statement can also be written as,
INSERT INTO employee
SELECT * FROM temp_employee;

Truncate =delete records
Delete=delete table tempor
Drop=alles tables
DELETE FROM employee WHERE id = 100;
TRUNCATE TABLE table_name;
DROP TABLE table_name;

ALTER TABLE table_name ADD column_name datatype;
ALTER TABLE table_name DROP column_name;
ALTER TABLE table_name MODIFY column_name datatype;
RENAME old_table_name To new_table_name;
The constraints available in SQL are Foreign Key, Not Null,Unique, Check.
Constraints can be defined in two ways
1) The constraints can be specified immediately after the column definition. This is called column-level definition.
2) The constraints can be specified after all the columns are defined. This is called table-level definition.

The Cartesian product returns a number of rows equal to the product of all rows in all the tables being joined. For example, if the first table has 20 rows and the second table has 10 rows, the result will be 20 * 10, or 200 rows. This query takes a long time to execute.

CREATE VIEW view_product
AS
SELECT product_id, product_name
FROM product;
SELECT id, first_name
FROM student_details
WHERE first_name IN (SELECT first_name
FROM student_details
WHERE subject= 'Science');

select p.product_name, p.supplier_name, (select order_id from order_items where product_id = 101) as order_id from product p where p.product_id = 101
SELECT p.product_name FROM product p
WHERE p.product_id = (SELECT o.product_id FROM order_items o
WHERE o.product_id = p.product_id);

* AVG() - Returns the average value * COUNT() - Returns the number of rows * FIRST() - Returns the first value * LAST() - Returns the last value * MAX() - Returns the largest value * MIN() - Returns the smallest value * SUM() - Returns the sum

SQL Scalar functions
SQL scalar functions return a single value, based on the input value.
Useful scalar functions: * UCASE() - Converts a field to upper case * LCASE() - Converts a field to lower case * MID() - Extract characters from a text field * LEN() - Returns the length of a text field * ROUND() - Rounds a numeric field to the number of decimals specified * NOW() - Returns the current system date and time * FORMAT() - Formats how a field is to be displayed * The UNION operator is used to combine the result-set of two or more SELECT statements.

An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them.
The most common type of join is: SQL INNER JOIN (simple join). An SQL INNER JOIN return all rows from multiple tables where the join condition is met.

* Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

select top(10) * from Employee
SELECT TOP 1 T.* FROM
(SELECT TOP 5 * FROM DBT_Person ORDER BY AGE ASC) AS T
ORDER BY T.AGE DESC;
5th entry