Web Development Course:
This is a SQL commands list that covers all the necessary actions with SQL databases. Each SQL command is provided with its definition, a code snippet that represents the correct syntax, and some have live code examples that you can try modifying to see the command in action.
- 1. SQL Commands List
- 1.1. AND|OR
- 1.2. ALTER TABLE
- 1.3. AS (alias)
- 1.4. BETWEEN
- 1.5. CREATE DATABASE
- 1.6. CREATE TABLE
- 1.7. CREATE INDEX
- 1.8. CREATE VIEW
- 1.9. DELETE
- 1.10. GRANT
- 1.11. REVOKE
- 1.12. COMMIT
- 1.13. ROLLBACK
- 1.14. SAVEPOINT
- 1.15. DROP DATABASE
- 1.16. DROP INDEX
- 1.17. DROP TABLE
- 1.18. EXISTS
- 1.19. GROUP BY
- 1.20. HAVING
- 1.21. IN
- 1.22. INSERT INTO
- 1.23. INNER JOIN
- 1.24. LEFT JOIN
- 1.25. RIGHT JOIN
- 1.26. FULL JOIN
- 1.27. LIKE
- 1.28. ORDER BY
- 1.29. SELECT
- 1.30. SELECT *
- 1.31. SELECT DISTINCT
- 1.32. SELECT INTO
- 1.33. SELECT TOP
- 1.34. TRUNCATE TABLE
- 1.35. UNION
- 1.36. UNION ALL
- 1.37. UPDATE
- 1.38. WHERE
SQL Commands List
Note: some of the examples have no output because they deal with deleting or creating whole tables.
AND combines two or more conditions for a single query. All of the conditions used with this operator must be met in order to show the results.
SELECT * FROM Developers WHERE Country='India' AND City='Delhi';
OR is used similarly, but it will output result with rows that comply with either of the conditions.
SELECT * FROM Developers WHERE City='London' OR City='Paris';
ALTER TABLE allows you to add or remove columns from a table.
ALTER TABLE Developers ADD BirthDate date;
ALTER TABLE Developers DROP COLUMN BirthDate;
AS allows you to rename a column or table to a more convenient alias (a correlation name) without changing the original names in the database. This makes writing queries easier when the original table or column names are long or complicated.
SELECT ID as CustomerID, Name AS Customers FROM Customers;
SELECT o.ID, c.Name FROM Customers AS c, Customer_orders AS o WHERE c.id = 2 AND c.ID = o.customer_id;
BETWEEN operator filters the results and returns only the ones that fit the specified range. You can describe the value of this operator using dates, numbers, or text.
SELECT * FROM Orders WHERE Price BETWEEN 10 AND 15;
When you need to create a new database, use the CREATE DATABASE statement. You must have admin rights to do that.
CREATE DATABASE testingDB;
CREATE TABLE statement creates a new table in a database.
CREATE TABLE Suppliers ( SupplierID int, FirstName varchar(255), LastName varchar(255), City varchar(255), Country varchar(255) );
CREATE INDEX generates an index for a table. This enables retrieving data from a database faster. Users don't see indexes as they are only used to increase search speed.
CREATE INDEX idx_lastname ON Persons (LastName);
CREATE VIEW creates a narrower version of an existing table by getting a set of results based on a certain query. A view is not much different from a real table: it contains columns and rows with data, but it doesn't contain the fields of the real table that are irrelevant for your particular purpose.
CREATE VIEW [Present List Products] AS SELECT ID, Name FROM Products WHERE Discontinued = No;
If you need to remove certain rows from the table, use the DELETE FROM statement.
DELETE FROM Developers WHERE Name='Antonio Indigo';
DELETE * FROM Developers;
GRANT command is for giving users the access to a database.
GRANT SELECT, UPDATE ON YOUR_TABLE TO FIRST_USER, SECOND_USER;
REVOKE command is for taking away users' permisions.
REVOKE SELECT, UPDATE ON YOUR_TABLE FROM FIRST_USER, SECOND_USER;
COMMIT command is for saving every transaction to the database.
DELETE FROM CUSTOMERS WHERE AGE = 18; COMMIT;
ROLLBACK command is for undoing transactions which are not saved to the database.
DELETE FROM CUSTOMERS WHERE AGE = 18; ROLLBACK;
SAVEPOINT command is for returning a transaction to a specific point without affecting the whole transaction.
DROP DATABASE is one of the riskiest statements that should be used with extra caution. In SQL, drop means delete – and DROP DATABASE deletes the whole specified database together with all its parameters and data.
DROP DATABASE db_name
DROP INDEX will delete the index you specify. The syntax of this statement varies based on the DB system used.
DROP INDEX tbl_name.indx_name
DROP INDEX indx_name ON tbl_name
DROP INDEX indx_name
ALTER TABLE tbl_name DROP INDEX indx_name
DROP TABLE statement deletes the whole table with its column parameters and datatype settings. If you want to remove only the contents of the rows but keep the table itself, use another statement – TRUNCATE TABLE.
DROP TABLE tbl_name
EXISTS operator allows you to check whether a record exists by writing a subquery. If the record is found, the result is displayed based on the statement you use this operator with. You can use it with SELECT, UPDATE, INSERT, and DELETE.
SELECT id, name FROM customers WHERE EXISTS (SELECT id FROM customer_orders WHERE customer_orders.customer_id = customers.id AND customers.city = "Rome");
Combine GROUP BY with SELECT statement in order to arrange identical data (rows with the same value) into groups (summarizing rows).
SELECT COUNT(ID), City FROM Developers GROUP BY City;
HAVING specifies that you need to filter the results to only the rows that fulfill the described condition.
It performs the same action as the WHERE clause. The difference is that HAVING is used only for aggregate functions as WHERE doesn't work with them.
SELECT COUNT(ID), Country FROM Pets GROUP BY Country HAVING COUNT(ID) > 2;
The IN operator includes multiple values into the WHERE clause.
SELECT * FROM Developers WHERE Country IN ('USA', 'France', 'India');
INSERT INTO statement inserts new rows of data into a table.
INSERT INTO Developers (Name, City, Country) VALUES ('Luke Christon', 'London', 'UK');
INNER JOIN combines rows from different tables.
SELECT Orders.ID, Developers.Name FROM Orders INNER JOIN Developers ON Orders.ID = Developers.ID;
LEFT JOIN retrieves records from the left table that match records in the right table. Some databases have a slightly different statement for this – LEFT OUTER JOIN.
SELECT Developers.Name, Customer_orders.ID FROM Developers LEFT JOIN Customer_orders ON Developers.ID = Customer_orders.customer_id ORDER BY Developers.Name;
RIGHT JOIN retrieves records from the right table that match records in the left table. Some databases call this statement differently – RIGHT OUTER JOIN.
SELECT Customer_orders.ID, Employees.Last_name, Employees.First_name FROM Customer_orders RIGHT JOIN Employees ON Customer_orders.employee_id = Employees.ID ORDER BY Customer_orders.ID;
FULL JOIN returns all the records that match either in left or right tables.
SELECT Customers.Name, Customer_orders.ID FROM Customers FULL OUTER JOIN Orders ON Customers.ID=Customer_orders.customer_id ORDER BY Customers.Name;
Combine LIKE with the WHERE clause for finding specific patterns in columns.
SELECT * FROM users WHERE email LIKE '%gmail%';
ORDER BY sets the order (ascending by default) of result records.
SELECT * FROM users ORDER BY email DESC;
SELECT is one of the main SQL statements. It selects data from a database and returns the table of results, called the result-set.
SELECT username,email FROM users;
SELECT used with an asterisk
* operator selects all data records from a specified table.
SELECT * FROM Customers;
SELECT DISTINCT returns only the data that is distinct, and does not include duplicate entries.
SELECT DISTINCT City FROM Developers;
SELECT INTO statement selects specified data in a table and copies it to another table.
SELECT * INTO CustomerBackup2018 FROM Customers;
SELECT Name, Contact INTO CustomerBackup2017 FROM Customers;
SELECT TOP specifies the maximum number or percentage of data entries to return in a result-set.
SELECT * FROM Customers LIMIT 3;
SELECT TOP 50 PERCENT * FROM Customers;
TRUNCATE TABLE removes data entries from a table in a database, but keeps the table, its datatype and column parameters.
TRUNCATE TABLE tbl_name
You can combine multiple result-sets using the UNION operator with two or more SELECT statements.
SELECT City FROM Developers UNION SELECT City FROM Customers ORDER BY City;
UNION ALL is used to combine two or more result-sets and keep all the duplicate data entries.
SELECT City FROM Developers UNION ALL SELECT City FROM Customers ORDER BY City;
UPDATE statement is used with the WHERE clause to update data in the table.
UPDATE Developers SET City = 'Berlin', Country= 'Germany' WHERE Name = 'Antonio Indigo';
WHERE clause specifies your query to filter only the results that satisfy your set condition.
WHERE doesn't work with the aggregate functions, for that purpose, use HAVING instead.
SELECT * FROM Developers WHERE Country='France';