1. SQL SELECT:
The SQL SELECT statement is used to retrieve data from one or more database tables. It allows you to specify the columns you want to retrieve and can also include conditions, sorting, and grouping.
Example:
SELECT column1, column2 FROM table_name;
This query selects the specified columns from the table.
2. SQL SELECT and SELECT WHERE:
The SQL SELECT statement can be combined with the WHERE clause to retrieve specific records that meet certain conditions.
Example:
SELECT column1, column2 FROM table_name WHERE condition;
This query selects the specified columns from the table where the specified condition is true.
3. SQL AND, OR, and NOT:
The SQL AND, OR, and NOT operators are used to combine multiple conditions in the WHERE clause.
Example:
SELECT column1, column2 FROM table_name WHERE condition1 AND condition2; SELECT column1, column2 FROM table_name WHERE condition1 OR condition2; SELECT column1, column2 FROM table_name WHERE NOT condition;
These queries select the specified columns from the table where the specified conditions are met using logical operators.
4. SQL SELECT DISTINCT:
The SQL SELECT DISTINCT statement is used to retrieve unique values from a column in a table.
Example:
SELECT DISTINCT column FROM table_name;
This query selects distinct values from the specified column in the table.
5. SQL SELECT AS:
The SQL SELECT AS statement is used to assign an alias to a column or a table.
Example:
SELECT column AS alias FROM table_name; SELECT column1, column2 FROM table_name AS alias;
These queries select columns from the table and assign aliases to them.
6. SQL LIMIT, TOP, and FETCH FIRST:
The SQL LIMIT, TOP, and FETCH FIRST clauses are used to limit the number of rows returned by a query.
Example:
SELECT column1, column2 FROM table_name LIMIT n; SELECT TOP n column1, column2 FROM table_name; SELECT column1, column2 FROM table_name FETCH FIRST n ROWS ONLY;
These queries select a specified number of rows from the table.
7. SQL IN Operator:
The SQL IN operator is used to specify multiple values in a WHERE clause.
Example:
SELECT column1, column2 FROM table_name WHERE column IN (value1, value2, ...);
This query selects rows from the table where the specified column value matches any of the given values.
8. SQL BETWEEN Operator:
The SQL BETWEEN operator is used to select values within a specified range.
Example:
SELECT column1, column2 FROM table_name WHERE column BETWEEN value1 AND value2;
This query selects rows from the table where the specified column value is within the specified range.
9. SQL IS NULL and NOT NULL:
The SQL IS NULL and NOT NULL operators are used to check for null values in a column.
Example:
SELECT column1, column2 FROM table_name WHERE column IS NULL; SELECT column1, column2 FROM table_name WHERE column IS NOT NULL;
These queries select rows from the table where the specified column value is null or not null, respectively.
10. SQL MIN() and MAX():
The SQL MIN() and MAX() functions are used to retrieve the minimum and maximum values from a column, respectively.
Example:
SELECT MIN(column) AS min_value FROM table_name; SELECT MAX(column) AS max_value FROM table_name;
11. SQL COUNT():
The SQL COUNT() function is used to count the number of rows that match a specific condition in a table.
Example:
SELECT COUNT(column_name) FROM table_name WHERE condition;
This query counts the number of rows in the specified table that satisfy the given condition and returns the result.
Example with DISTINCT:
SELECT COUNT(DISTINCT column_name) FROM table_name WHERE condition;
This query counts the number of distinct values in the specified column of the table that satisfy the given condition and returns the result.
Example with Alias:
SELECT COUNT(column_name) AS count_alias FROM table_name WHERE condition;
This query assigns an alias “count_alias” to the result of the COUNT() function for better readability or further use in the query.
Note: The COUNT() function can also be used without a specific column name to count the total number of rows in a table.
12. SQL ORDER BY:
The SQL ORDER BY clause is used to sort the result set based on one or more columns in ascending or descending order.
Example:
SELECT column1, column2 FROM table_name ORDER BY column1 ASC;
This query retrieves columns from the table and sorts the result set based on the values in column1 in ascending order.
13. SQL GROUP BY:
The SQL GROUP BY clause is used to group rows based on one or more columns and apply aggregate functions to each group.
Example:
SELECT column1, COUNT(column2) FROM table_name GROUP BY column1;
This query groups rows based on the values in column1 and applies the COUNT() function to each group, returning the count of values in column2 for each group.
14. SQL LIKE:
The SQL LIKE operator is used in the WHERE clause to search for a specified pattern in a column.
Example:
SELECT column1, column2 FROM table_name WHERE column1 LIKE 'abc%';
This query retrieves columns from the table where the value in column1 starts with ‘abc’.
15. SQL Wildcards:
SQL wildcards (%, _) are special characters used with the LIKE operator to match patterns.
SELECT column1, column2 FROM table_name WHERE column1 LIKE 'a%'; -- Matches any value starting with 'a' SELECT column1, column2 FROM table_name WHERE column1 LIKE '%b'; -- Matches any value ending with 'b' SELECT column1, column2 FROM table_name WHERE column1 LIKE '%c%'; -- Matches any value containing 'c'
These queries retrieve columns from the table based on specific patterns using wildcards.
16. SQL UNION:
The SQL UNION operator is used to combine the result sets of two or more SELECT statements into a single result set.
Example:
SELECT column1, column2 FROM table1 UNION SELECT column1, column2 FROM table2;
This query combines the result sets of two SELECT statements from different tables, removing duplicates, and returns a single result set.
17. SQL Subquery:
A SQL subquery is a query nested inside another query and is used to retrieve data based on the result of the inner query.
Example:
SELECT column1, column2 FROM table_name WHERE column1 IN (SELECT column1 FROM table2 WHERE condition);
This query retrieves columns from the table where the value in column1 matches any value returned by the subquery.
18. SQL ANY and ALL:
The SQL ANY and ALL operators are used with a subquery to compare a value with a set of values.
Example:
SELECT column1, column2 FROM table_name WHERE column1 > ANY (SELECT column1 FROM table2 WHERE condition);
This query retrieves columns from the table where the value in column1 is greater than any value returned by the subquery.
19. SQL CASE:
The SQL CASE statement is used to perform conditional logic in SQL queries.
Example:
SELECT column1, CASE WHEN condition1 THEN 'Value1' WHEN condition2 THEN 'Value2' ELSE 'Value3' END AS new_column FROM table_name;
This query selects column1 from the table and uses the CASE statement to assign a new value to the new_column based on specified conditions.
20. SQL HAVING:
The SQL HAVING clause is used to filter groups in the result set when using the GROUP BY clause.
Example:
SELECT column1, COUNT(column2) FROM table_name GROUP BY column1 HAVING COUNT(column2) >
21. SQL JOINS:
SQL JOINS are used to combine rows from two or more tables based on related columns between them. There are several types of joins: INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.
22. SQL INNER JOIN:
The SQL INNER JOIN returns the rows that have matching values in both tables involved in the join.
Example:
SELECT table1.column1, table2.column2 FROM table1 INNER JOIN table2 ON table1.id = table2.id;
This query performs an INNER JOIN between table1 and table2 based on the common “id” column and retrieves specified columns from both tables.
23. SQL LEFT JOIN:
The SQL LEFT JOIN returns all rows from the left table and the matching rows from the right table. If there is no match, NULL values are returned for the right table.
Example:
SELECT table1.column1, table2.column2 FROM table1 LEFT JOIN table2 ON table1.id = table2.id;
This query performs a LEFT JOIN between table1 and table2 based on the common “id” column and retrieves specified columns from both tables.
24. SQL RIGHT JOIN:
The SQL RIGHT JOIN returns all rows from the right table and the matching rows from the left table. If there is no match, NULL values are returned for the left table.
Example:
SELECT table1.column1, table2.column2 FROM table1 RIGHT JOIN table2 ON table1.id = table2.id;
This query performs a RIGHT JOIN between table1 and table2 based on the common “id” column and retrieves specified columns from both tables.
25. SQL FULL OUTER JOIN:
The SQL FULL OUTER JOIN returns all rows from both tables involved in the join, regardless of whether there is a match or not. If there is no match, NULL values are returned for the non-matching table.
Example:
SELECT table1.column1, table2.column2 FROM table1 FULL OUTER JOIN table2 ON table1.id = table2.id;
This query performs a FULL OUTER JOIN between table1 and table2 based on the common “id” column and retrieves specified columns from both tables, including unmatched rows.
Note: The syntax and availability of different join types may vary depending on the specific database management system (DBMS) being used.
26. SQL Create Database:
The SQL CREATE DATABASE statement is used to create a new database.
Example:
CREATE DATABASE database_name;
This query creates a new database with the specified name.
27. SQL Create Table:
The SQL CREATE TABLE statement is used to create a new table within a database.
Example:
CREATE TABLE table_name ( column1 datatype, column2 datatype, ... );
This query creates a new table with the specified columns and data types.
28. SQL Drop Database:
The SQL DROP DATABASE statement is used to delete an existing database.
Example:
DROP DATABASE database_name;
This query deletes the specified database and all its associated tables and data.
29. SQL Drop Table:
The SQL DROP TABLE statement is used to delete an existing table.
Example:
DROP TABLE table_name;
This query deletes the specified table and all its data.
30. SQL Alter Table:
The SQL ALTER TABLE statement is used to modify an existing table structure.
Example:
ALTER TABLE table_name ADD column datatype; ALTER TABLE table_name DROP COLUMN column_name; ALTER TABLE table_name MODIFY column datatype;
These queries modify the specified table by adding a new column, dropping an existing column, or modifying the data type of a column.
31. SQL Backup Database:
The SQL BACKUP DATABASE statement is used to create a backup of an existing database.
Example:
BACKUP DATABASE database_name TO disk = 'backup_location';
This query creates a backup of the specified database and stores it at the specified backup location.
Note: The exact syntax and options for creating backups may vary depending on the specific database management system (DBMS) being used.
32. SQL Insert Into:
The SQL INSERT INTO statement is used to insert new records into a table.
Example:
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
This query inserts a new record into the specified table with the provided values for the specified columns.
33. SQL Update:
The SQL UPDATE statement is used to modify existing records in a table.
Example:
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
This query updates the specified columns in the table with the provided values based on the specified condition.
34. SQL Select Into:
The SQL SELECT INTO statement is used to create a new table from the result of a SELECT query.
Example:
SELECT column1, column2, ... INTO new_table FROM table_name WHERE condition;
This query creates a new table named “new_table” with the specified columns and inserts the result of the SELECT query into it.
35. SQL Select Into Insert:
The SQL SELECT INTO INSERT statement is used to insert data into an existing table from the result of a SELECT query.
Example:
INSERT INTO table_name (column1, column2, ...) SELECT column1, column2, ... FROM another_table WHERE condition;
This query selects data from “another_table” based on the specified condition and inserts it into “table_name” for the specified columns.
36. SQL Delete and Truncate Rows:
The SQL DELETE statement is used to delete specific records from a table based on a condition.
Example:
DELETE FROM table_name WHERE condition;
This query deletes records from the table based on the specified condition.
The SQL TRUNCATE TABLE statement is used to delete all records from a table, effectively emptying it.
Example:
TRUNCATE TABLE table_name;
This query removes all records from the specified table, but the table structure remains intact.
Note: It’s important to exercise caution when performing delete and truncate operations as they can permanently remove data from the database. Always make sure to have backups and double-check the conditions before executing these statements.
37. SQL Not Null Constraint:
The SQL NOT NULL constraint is used to ensure that a column does not contain any NULL values.
Example:
CREATE TABLE table_name ( column1 datatype NOT NULL, column2 datatype, ... );
This query creates a table with the specified columns, and the NOT NULL constraint is applied to column1, ensuring it cannot contain NULL values.
38. SQL Unique Constraints:
The SQL UNIQUE constraint is used to ensure that a column or a combination of columns have unique values.
Example:
CREATE TABLE table_name ( column1 datatype, column2 datatype, ... CONSTRAINT constraint_name UNIQUE (column1, column2, ...) );
This query creates a table with the specified columns, and the UNIQUE constraint is applied to column1, column2, etc., ensuring their values are unique.
39. SQL Primary Key:
The SQL PRIMARY KEY constraint is used to uniquely identify each record in a table.
Example:
CREATE TABLE table_name ( column1 datatype PRIMARY KEY, column2 datatype, ... );
This query creates a table with the specified columns, and column1 is designated as the primary key, ensuring its values are unique and not NULL.
40. SQL Foreign Key:
The SQL FOREIGN KEY constraint is used to establish a relationship between two tables based on a column in each table.
Example:
CREATE TABLE table1 ( column1 datatype PRIMARY KEY, ... ); CREATE TABLE table2 ( column1 datatype, column2 datatype, ... CONSTRAINT constraint_name FOREIGN KEY (column1) REFERENCES table1(column1) );
This query creates two tables, and the FOREIGN KEY constraint is applied to column1 in table2, referencing the primary key column1 in table1, establishing a relationship between the two tables.
41. SQL Check:
The SQL CHECK constraint is used to define a condition that must be met for the values in a column.
Example
CREATE TABLE table_name ( column1 datatype, column2 datatype CHECK (condition), ... );
This query creates a table with the specified columns, and the CHECK constraint is applied to column2, ensuring that its values satisfy the specified condition.
42. SQL Default:
The SQL DEFAULT constraint is used to set a default value for a column when no value is specified during an INSERT operation.
Example:
CREATE TABLE table_name ( column1 datatype DEFAULT default_value, column2 datatype, ... );
This query creates a table with the specified columns, and the DEFAULT constraint is applied to column1, setting it to the default_value when no value is provided.
43. SQL Create Index:
The SQL CREATE INDEX statement is used to create an index on one or more columns in a table, which improves the query performance.
Example:
CREATE INDEX index_name ON table_name (column1, column2, ...);
This query creates an index named index_name on the specified columns in the table, which allows for faster retrieval of data when querying based on those columns.
Note: Constraints, such as NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT, can be applied during table creation or added/modified later using the ALTER TABLE statement.
44. SQL Data Types:
SQL Data Types are used to define the type of data that can be stored in a column of a table.
Example:
CREATE TABLE table_name ( column1 datatype, column2 datatype, ... );
In this query, you can replace datatype with specific data types like INTEGER, VARCHAR, DATE, etc., based on the requirements of the columns.
45. SQL Date and Time:
SQL provides specific data types and functions to handle dates and times.
Example:
SELECT column1, column2 FROM table_name WHERE column_date >= CURRENT_DATE;
This query retrieves columns from the table where the value in the column_date is greater than or equal to the current date.
46. SQL Operators:
SQL Operators are used to perform various operations in SQL queries, such as arithmetic operations, comparison operations, logical operations, etc.
Example:
SELECT column1, column2 FROM table_name WHERE column1 > 10 AND column2 = 'abc';
This query retrieves columns from the table where the value in column1 is greater than 10 and the value in column2 is ‘abc’.
47. SQL Comments:
SQL Comments are used to add explanatory or descriptive notes within SQL code that are ignored by the database engine.
Example:
-- This is a single-line comment /* This is a multi-line comment It can span multiple lines */ SELECT column1, column2 FROM table_name; -- This is a comment at the end of a line
In this example, comments are added using ‘–‘ for single-line comments and ‘/* */’ for multi-line comments.
48. SQL Views:
SQL Views are virtual tables that are derived from one or more tables or other views.
Example:
CREATE VIEW view_name AS SELECT column1, column2 FROM table_name WHERE condition;
This query creates a view named view_name that contains the selected columns from table_name based on the specified condition.
49. SQL Stored Procedures:
SQL Stored Procedures are sets of SQL statements that are stored in the database and can be executed repeatedly.
Example:
CREATE PROCEDURE procedure_name AS BEGIN -- SQL statements END;
This query creates a stored procedure named procedure_name, which can contain multiple SQL statements and can be called later to execute those statements.
50. SQL Injection:
SQL Injection is a security vulnerability that occurs when an attacker inserts malicious SQL code into a query, potentially allowing unauthorized access or manipulation of the database.
Example (Vulnerable Query):
SELECT column1, column2 FROM table_name WHERE username = ' + userInput + ' AND password = ' + userInput;
In this example, the query is vulnerable to SQL injection because it directly concatenates user input into the SQL statement. To prevent SQL injection, parameterized queries or prepared statements should be used.
Note: It is important to implement proper security measures, such as input validation, parameterized queries, and user access controls, to prevent SQL injection attacks.