SQL Tutorial | SQL Syntax and Examples

Please click on "Easy Link" Menu for section wise easy navigation.

Please click on "Blogs" Menu for descriptions regarding SQL issues and their solutions, SQL tricks and explanations on various SQL topics with examples.

Downloading SQL Server:

1. Visit Microsoft's SQL Server Download Page: Go to Microsoft SQL Server Downloads on your web browser.

2. Choose the Edition: Select the edition of SQL Server you want to download. Microsoft offers various editions such as Express, Developer, Standard, and Enterprise. Each edition has different features and limitations.

3. Select the Version: Choose the version you prefer. Generally, it's recommended to go for the latest stable version unless you have specific requirements.

4. Click on Download: After selecting the edition and version, click the download button.

Installing SQL Server:

Once the download is complete, follow these steps to install:

1. Run the Installer: Locate the downloaded file and run the installer by double-clicking on it.

2. Setup Wizard: The installer will launch a setup wizard. Follow the on-screen instructions. You might need to agree to the terms and conditions, specify installation preferences, and configure settings such as instance name, authentication mode, etc.

3. Feature Selection: Select the features you want to install. The default options are usually fine for most users, but you can customize as needed.

4. Instance Configuration: Choose the instance configuration. The default instance name is typically "MSSQLSERVER," but you can specify a different name if required.

5. Authentication Mode: Set the authentication mode. You can choose between Windows Authentication mode (often called Integrated Security) or Mixed Mode (SQL Server Authentication and Windows Authentication).

6. Specify Service Accounts: Assign service accounts for the SQL Server services. You can use built-in accounts or specify custom accounts.

7. Configuration Settings: Configure additional settings such as data directories, collation settings, and other options based on your preferences.

8. Installation: Once you've configured all settings, proceed with the installation. The process might take some time depending on the chosen options and your system's performance.

9. Completion: After the installation completes successfully, you should see a confirmation message. You can then access SQL Server Management Studio (SSMS) to start using SQL Server.

Data Definition Language (DDL) SQL commands:

Data Definition Language (DDL) SQL commands along with their syntax and examples:

1. CREATE

   Used to create database objects like tables, views, indexes, etc.

   Syntax: CREATE [OBJECT_TYPE] object_name (column1 datatype, column2 datatype, ...);

   Example: CREATE TABLE employees ( emp_id INT PRIMARY KEY, emp_name VARCHAR(50), emp_salary DECIMAL(10, 2) );

2. ALTER

   Modifies an existing database object.

   Syntax: ALTER [OBJECT_TYPE] object_name action;

   Example: ALTER TABLE employees ADD emp_address VARCHAR(100);

3. DROP

   Deletes an existing database object.

   Syntax: DROP [OBJECT_TYPE] object_name;

   Example: DROP TABLE employees;

4. TRUNCATE

   Removes all records from a table, but keeps the table structure.

   Syntax: TRUNCATE TABLE table_name;

   Example: TRUNCATE TABLE employees;

5. RENAME

   Renames an existing database object.

   Syntax: RENAME [OBJECT_TYPE] old_name TO new_name;

   Example: RENAME TABLE employees TO staff;

6. COMMENT

   Adds comments to the data dictionary.

   Syntax: COMMENT ON [OBJECT_TYPE] object_name IS 'comment_text';

   Example: COMMENT ON COLUMN employees.emp_name IS 'Employee Full Name';

7. CREATE INDEX

   Creates an index on a table.

   Syntax: CREATE INDEX index_name ON table_name (column1, column2, ...);

   Example: CREATE INDEX idx_emp_name ON employees (emp_name);

DML (Data Manipulation Language) commands:

DML stands for Data Manipulation Language and includes commands in SQL that deal with managing data within a database. Here are the primary DML commands along with their syntax and examples:

1. INSERT

• Syntax: INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

• Example: INSERT INTO employees (name, age, department) VALUES ('John Doe', 30, 'Sales');

2. SELECT

• Syntax: SELECT column1, column2, ... FROM table_name WHERE condition;

• Example: SELECT * FROM employees WHERE department = 'Sales';

3. UPDATE

• Syntax: UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;

• Example: UPDATE employees SET age = 31 WHERE name = 'John Doe';

4. DELETE

• Syntax: DELETE FROM table_name WHERE condition;

• Example: DELETE FROM employees WHERE age > 65;

5. MERGE (Depending on the SQL variant)

• Syntax: MERGE INTO target_table USING source_table ON merge_condition WHEN MATCHED THEN UPDATE SET column1 = value1, column2 = value2, ... WHEN NOT MATCHED THEN INSERT (column1, column2, ...) VALUES (value1, value2, ...);

• Example: MERGE INTO target_table t USING source_table s ON (t.id = s.id) WHEN MATCHED THEN UPDATE SET t.column1 = s.column1 WHEN NOT MATCHED THEN INSERT (column1, column2, ...) VALUES (value1, value2, ...);

Data Control Language (DCL) commands:

Data Control Language (DCL) commands are used to manage permissions, access rights, and security within a database. Here are some common DCL commands along with their syntax and examples:

1. GRANT

   Provides specific privileges to users or roles.

   Syntax: GRANT privilege_name ON object_name TO {user_name | PUBLIC | role_name} [WITH GRANT OPTION];

   Example: Granting SELECT privilege on a table to a user: GRANT SELECT ON employees TO user1;

2. REVOKE

   Removes specific privileges from users or roles.

   Syntax: REVOKE privilege_name ON object_name FROM {user_name | PUBLIC | role_name};

   Example: Revoking INSERT privilege on a table from a user: REVOKE INSERT ON products FROM user2;

3. DENY

   Note : Some databases like SQL Server use DENY to explicitly prevent certain permissions

   Syntax (SQL Server): DENY permission ON object_name TO {user_name | role_name | PUBLIC};

   Example (SQL Server): Denying DELETE permission on a table from a user: DENY DELETE ON customers TO user3;

Transactional Control Language (TCL) commands:

Transactional Control Language (TCL) commands in SQL are used to manage transactions within a database. The key TCL commands include COMMIT, ROLLBACK, and SAVEPOINT. Below are their syntaxes and examples:

1. COMMIT

• Syntax: COMMIT;

• Example: Committing a transaction to make changes permanent: BEGIN TRANSACTION; -- SQL statements COMMIT;

2. ROLLBACK

• Syntax: ROLLBACK;

• Example: Rolling back a transaction to undo changes:

  BEGIN TRANSACTION; -- SQL statements ROLLBACK;

3. SAVEPOINT

• Syntax: SAVEPOINT savepoint_name;

• Example: Creating a savepoint within a transaction: BEGIN TRANSACTION; -- SQL statements SAVEPOINT sp1; -- More SQL statements ROLLBACK TO SAVEPOINT sp1;

SQL Functions :

SQL functions are powerful tools that perform specific tasks and return a single value. There are various types of functions in SQL, including aggregate functions, string functions, numeric functions, date/time functions, and more. Here's a list with examples for some commonly used SQL functions:

1. Aggregate Functions:

• COUNT()

  • Syntax: COUNT(expression)

  • Example: SELECT COUNT(*) FROM table_name;

• SUM()

  • Syntax: SUM(column_name)

  • Example: SELECT SUM(sales_amount) FROM sales;

• AVG()

  • Syntax: AVG(column_name)

  • Example: SELECT AVG(age) FROM users;

• MIN()

  • Syntax: MIN(column_name)

  • Example: SELECT MIN(price) FROM products;

• MAX()

  • Syntax: MAX(column_name)

  • Example: SELECT MAX(score) FROM exams;

2. String Functions

• CONCAT()

  • Syntax: CONCAT(string1, string2, ...)

  • Example: SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

• SUBSTRING()

  • Syntax: SUBSTRING(string, start, length)

  • Example: SELECT SUBSTRING(description, 1, 100) AS short_desc FROM products;

• UPPER()

  • Syntax: UPPER(string)

  • Example: SELECT UPPER(city) FROM customers;

• LOWER()

  • Syntax: LOWER(string)

  • Example: SELECT LOWER(email) FROM users;

3. Date Functions

• GETDATE() / CURRENT_TIMESTAMP

  • Syntax: GETDATE() or CURRENT_TIMESTAMP

  • Example: SELECT GETDATE();

• DATEADD()

  • Syntax: DATEADD(datepart, number, date)

  • Example: SELECT DATEADD(day, 7, order_date) AS new_date FROM orders;

• DATEDIFF()

  • Syntax: DATEDIFF(datepart, start_date, end_date)

  • Example: SELECT DATEDIFF(day, start_date, end_date) AS days_diff FROM events;

4. Mathematical Functions

• ROUND()

  • Syntax: ROUND(number, decimal_places)

  • Example: SELECT ROUND(price, 2) FROM products;

• ABS()

  • Syntax: ABS(number)

  • Example: SELECT ABS(balance) FROM accounts;

• POWER()

  • Syntax: POWER(number, exponent)

  • Example: SELECT POWER(2, 3) AS result;

5. Conditional Functions

• CASE

  • Syntax: CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE result END

  • Example: SELECT CASE WHEN age < 18 THEN 'Minor' ELSE 'Adult' END AS age_group FROM users;

6. Conversion Functions

• CAST()

  • Syntax: CAST(expression AS data_type)

  • Example: SELECT CAST(column_name AS VARCHAR(50)) FROM table_name;

• CONVERT()

  • Syntax: CONVERT(data_type, expression, optional_format)

  • Example: SELECT CONVERT(VARCHAR(20), date_column, 101) AS formatted_date FROM table;

7. NULL-related Functions

• COALESCE()

  • Syntax: COALESCE(value1, value2, ...)

  • Example: SELECT COALESCE(null_column, 'N/A') AS replaced_value FROM table;

• ISNULL()

  • Syntax: ISNULL(expression, replacement_value)

  • Example: SELECT ISNULL(null_column, 0) AS replaced_value FROM table;

8. Aggregate Functions (Advanced)

• GROUP_CONCAT()

  • Syntax: GROUP_CONCAT(column_name SEPARATOR 'separator')

  • Example: SELECT GROUP_CONCAT(product_name SEPARATOR ', ') AS products_list FROM order_details;

9. Full-Text Search Functions (Specific to Databases like MySQL)

• MATCH()

  • Syntax: MATCH(column_name) AGAINST ('search_term')

  • Example: SELECT * FROM articles WHERE MATCH(title, content) AGAINST ('database' IN BOOLEAN MODE);

10. JSON Functions (Available in databases supporting JSON data types like PostgreSQL, MySQL, etc.)

• JSON_VALUE()

  • Syntax: JSON_VALUE(json_column, '$.key')

  • Example: SELECT JSON_VALUE(customer_info, '$.name') AS customer_name FROM customers;

• JSON_ARRAYAGG()

  • Syntax: JSON_ARRAYAGG(expression)

  • Example: SELECT JSON_ARRAYAGG(product_name) AS products FROM products;

11. Geospatial Functions (Specific to databases supporting geospatial data types like PostgreSQL with PostGIS)

• ST_Distance()

  • Syntax: ST_Distance(geography_column1, geography_column2)

  • Example: SELECT ST_Distance(location1, location2) AS distance FROM places;

• ST_Intersects()

  • Syntax: ST_Intersects(geography_column1, geography_column2)

  • Example: SELECT * FROM regions WHERE ST_Intersects(region_polygon, user_location);

SQL Clauses :

Here's a list of SQL clauses along with their syntax and examples:

1. SELECT

• Syntax: SELECT column1, column2 FROM table_name WHERE condition;

• Example: SELECT * FROM employees WHERE department = 'Sales';

2. FROM

• Syntax: SELECT column1, column2 FROM table_name WHERE condition;

• Example: SELECT * FROM customers;

3. WHERE

• Syntax: SELECT column1, column2 FROM table_name WHERE condition;

• Example: SELECT * FROM products WHERE price > 50;

4. GROUP BY

• Syntax: SELECT column1, COUNT(column2) FROM table_name GROUP BY column1;

• Example: SELECT department, COUNT(*) FROM employees GROUP BY department;

5. HAVING

• Syntax: SELECT column1, COUNT(column2) FROM table_name GROUP BY column1 HAVING condition;

• Example: SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 5;

6. ORDER BY

• Syntax: SELECT column1, column2 FROM table_name ORDER BY column1 [ASC|DESC];

• Example: SELECT * FROM products ORDER BY price DESC;

7. JOIN

• Syntax: SELECT table1.column1, table2.column2 FROM table1 INNER JOIN table2 ON table1.common_field = table2.common_field;

• Example: SELECT customers.CustomerName, orders.OrderID FROM customers INNER JOIN orders ON customers.CustomerID = orders.CustomerID;

8. UNION

• Syntax: SELECT column1 FROM table1 UNION SELECT column2 FROM table2;

• Example: SELECT city FROM customers UNION SELECT city FROM suppliers;

9. DISTINCT

• Syntax: SELECT DISTINCT column1, column2 FROM table_name;

• Example: SELECT DISTINCT department FROM employees;

10. LIMIT (or TOP in some databases)

• Syntax: SELECT column1 FROM table_name LIMIT n;

• Example: SELECT * FROM products LIMIT 10;

11. OFFSET (for pagination)

• Syntax: SELECT column1 FROM table_name LIMIT n OFFSET m;

• Example: SELECT * FROM products LIMIT 10 OFFSET 20;

12. INSERT INTO

• Syntax: INSERT INTO table_name (column1, column2) VALUES (value1, value2);

• Example: INSERT INTO customers (CustomerName, ContactName) VALUES ('Company Inc', 'John Doe');

13. UPDATE

• Syntax: UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

• Example: UPDATE products SET price = 25 WHERE product_id = 1;

14. DELETE

• Syntax: DELETE FROM table_name WHERE condition;

• Example: DELETE FROM customers WHERE customer_id = 5;

15. TRUNCATE

• Syntax: TRUNCATE TABLE table_name;

• Example: TRUNCATE TABLE employees;

JOINS & UNION

SQL joins are used to combine rows from different tables based on a related column between them. There are different types of joins in SQL:

1. INNER JOIN: Returns rows when there is a match in both tables.

   Syntax:

   SELECT columns FROM table1 INNER JOIN table2 ON table1.column = table2.column;

   Example: Consider two tables, employees and departments, where employees has a column dept_id that relates to the id column in departments.

   SELECT employees.name, departments.department_name FROM employees INNER JOIN departments ON employees.dept_id = departments.id;

2. LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table and matching rows from the right table.

   Syntax:

   SELECT columns FROM table1 LEFT JOIN table2 ON table1.column = table2.column;

   Example: Using the same tables as before:

   SELECT employees.name, departments.department_name FROM employees LEFT JOIN departments ON employees.dept_id = departments.id;

3. RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table and matching rows from the left table.

   Syntax:

   SELECT columns FROM table1 RIGHT JOIN table2 ON table1.column = table2.column;

   Example: Using the same tables:

   SELECT employees.name, departments.department_name FROM employees RIGHT JOIN departments ON employees.dept_id = departments.id;

4. FULL OUTER JOIN: Returns all rows when there is a match in either left or right table.

   Syntax:

   SELECT columns FROM table1 FULL OUTER JOIN table2 ON table1.column = table2.column;

   Example: Using the same tables:

   SELECT employees.name, departments.department_name FROM employees FULL OUTER JOIN departments ON employees.dept_id = departments.id;

5. CROSS JOIN: Returns the Cartesian product of rows from both tables (all possible combinations of rows).

   Syntax:

   SELECT columns FROM table1 CROSS JOIN table2;

   Example:

   SELECT employees.name, departments.department_name FROM employees CROSS JOIN departments;

Remember, the JOIN conditions are specified in the ON clause and determine how the tables are related. Each type of join serves a different purpose, so use them based on what kind of data retrieval you need!

6. UNION: In SQL, the UNION operator is used to combine the result sets of two or more SELECT statements into a single result set, removing any duplicate rows.

   The basic syntax for using UNION is:

   SELECT column1, column2, ... FROM table1 WHERE conditions UNION SELECT column1, column2, ... FROM table2 WHERE conditions;

   Here's a breakdown:

   • SELECT column1, column2, ...: Specifies the columns you want to retrieve.

   • FROM table1: Indicates the table or tables from which you're selecting data.

   • WHERE conditions: Represents any conditions to filter the data, which is optional for each SELECT statement.

   • UNION: Combines the results of the preceding SELECT statements.

   • The SELECT statements must have the same number of columns and compatible data types in corresponding columns.

   Here's a simple example to illustrate the usage of UNION:

   Let's say we have two tables, employees and managers, with similar structures:

   employees table:

   employee_id | employee_name

   1 | Alice

   2 | Bob

   3 | Charlie

   managers table:

   manager_id | manager_name

   101 | David

   102 | Emily

   103 | Alice

   If we want to retrieve a combined list of all employees and managers without duplicates, we can use UNION:

   SELECT employee_id, employee_name FROM employees UNION SELECT manager_id, manager_name FROM managers;

   This query will produce a result set like this:

   employee_id | employee_name

   1 | Alice

   2 | Bob

   3 | Charlie

   101 | David

   102 | Emily

   Notice that duplicate records (in this case, Alice appearing in both tables) are eliminated in the final result due to the use of UNION. If you want to include duplicates, you can use UNION ALL instead of UNION.

LIKE and Wildcard Character

In SQL, wildcard characters are used in conjunction with the LIKE operator to perform pattern matching in queries. There are mainly two wildcard characters commonly used:

1. % - Represents zero or more characters.

2. _ - Represents a single character.

Here's the syntax for using wildcards in SQL:

SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern;

• column_name(s) is the name of the column(s) you want to search.

• table_name is the name of the table you're querying.

• LIKE is the operator used for pattern matching.

• pattern is the pattern you're searching for, and it can contain wildcard characters.

Examples:

1. To find all records where the name starts with 'J':

   SELECT * FROM employees WHERE name LIKE 'J%';

   This query will retrieve all records from the employees table where the name column starts with 'J'.

2. To find all records where the name contains 'ohn':

   SELECT * FROM employees WHERE name LIKE '%ohn%';

   This query will retrieve all records from the employees table where the name column contains 'ohn' anywhere in the string.

3. To find all records where the name is exactly three characters long:

   SELECT * FROM employees WHERE name LIKE '___';

   This query will retrieve all records from the employees table where the name column is exactly three characters long.

PROCEDURE

Let's say you have a table called Employees with columns EmployeeID, FirstName, LastName, and Salary. You want to create a procedure that retrieves the employee details based on the EmployeeID.

CREATE PROCEDURE GetEmployeeDetails @EmpID INT AS

BEGIN -- Start of the procedure

SELECT EmployeeID, FirstName, LastName, Salary FROM Employees

WHERE EmployeeID = @EmpID; -- End of the procedure

END;

This procedure is named GetEmployeeDetails, and it takes one input parameter @EmpID of type INT. Within the procedure, it fetches the details of the employee whose EmployeeID matches the provided @EmpID.

To execute this stored procedure:

EXEC GetEmployeeDetails @EmpID = 123; -- Replace 123 with the desired EmployeeID

This will return the details of the employee with EmployeeID equal to 123.

Remember, stored procedures can contain multiple SQL statements, handle transactions, perform data validation, and more. They are often used to encapsulate logic for repeated execution within a database system.

Table Function

A table-valued function (TVF) in Microsoft SQL Server is a function that returns a table. Here's an example of how you might create a simple table-valued function:

Let's say we want to create a function that retrieves a list of employees whose salaries are above a certain threshold.

- Create a table-valued function

CREATE FUNCTION GetEmployeesAboveSalary ( @SalaryThreshold DECIMAL(10, 2) ) -- Input parameter for the salary threshold

RETURNS TABLE -- Specifies that the function returns a table

AS RETURN ( SELECT EmployeeID, FirstName, LastName, Salary FROM Employees WHERE Salary > @SalaryThreshold );

In this example, the function GetEmployeesAboveSalary takes @SalaryThreshold as an input parameter and returns a table containing employees whose salaries are greater than the provided threshold.

To use this table-valued function:

SELECT * FROM GetEmployeesAboveSalary(50000); -- Replace 50000 with the desired salary threshold

This will return a table containing the details of employees whose salaries are above $50,000 (or the provided threshold).

Table-valued functions can be very useful when you want to encapsulate complex logic that needs to return a result set or table, allowing you to reuse this logic in different queries.

VIEWS

In SQL, a view is a virtual table created by a query. It can be seen as a stored SELECT statement that allows you to retrieve data from one or multiple tables. Here's the syntax to create a view in Microsoft SQL Server:

CREATEVIEW view_name ASSELECT column1, column2, ... FROM table_name WHEREcondition;

Let's say you have two tables: employees and departments, and you want to create a view that shows the names of employees along with their department names. Here's an example:

CREATEVIEW EmployeeDepartments AS

SELECT e.employee_id, e.employee_name, d.department_name FROM employees e

INNERJOIN departments d ON e.department_id = d.department_id;

This creates a view named EmployeeDepartments that combines data from the employees and departments tables based on the department_id column.

To use this view and retrieve data from it, you can simply query it like you would a table:

SELECT * FROM EmployeeDepartments;

You can also modify views using the ALTER VIEW statement:

ALTERVIEW view_name AS

SELECT modified_columns FROM new_tables_joins WHERE conditions;

And drop a view using:

DROPVIEW view_name;

Views are helpful for simplifying complex queries, providing a layer of abstraction, and ensuring consistent data access across multiple users and applications without duplicating code.

Exception Handling

In Microsoft SQL Server, you can use the TRY...CATCH block to handle exceptions. This structure allows you to gracefully manage errors that might occur during the execution of your SQL code.

Here's an example of how you can use TRY...CATCH:

BEGIN TRY -- Your SQL statements that might throw an error

DECLARE @result INT; SET @result = 100 / 0; -- This will cause a divide by zero error -- If the above line throws an error, the code below will not execute

SELECT 'The result is: ' + CAST(@result AS VARCHAR); END TRY

BEGIN CATCH -- This block catches the error and handles it

SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState;

END CATCH;

Explanation:

• The TRY block contains the code that might generate an error. In this case, it attempts to divide by zero, which will cause a runtime error.

• If an error occurs within the TRY block, the control moves to the CATCH block.

• Inside the CATCH block, various ERROR_ functions are used to retrieve information about the error, such as the error number, error message, severity, and state.

You can customize the CATCH block to handle errors according to your requirements. For instance, you might log the error to a table, raise a custom error message, or perform other appropriate actions based on the type of error encountered.

Remember, error handling in SQL Server is crucial for robust and stable applications. It allows you to identify and address issues that might occur during the execution of your SQL code.

Pivot and Unvivot

Pivot in SQL

Pivoting involves rotating rows into columns. Here's the syntax for pivoting in SQL:

SELECT <non-pivoted column(s)>, [pivot_column1], [pivot_column2], ..., [pivot_columnN]

FROM

(SELECT <columns> FROM <table>) AS SourceTable

PIVOT ( <aggregate_function>(<value_column>) FOR <pivot_column> IN ([pivot_value1], [pivot_value2], ..., [pivot_valueN]) )

AS PivotTable;

Explanation:

• <non-pivoted column(s)>: Columns to be included in the result set.

• <columns>: Columns used in the pivot.

• <table>: The table from which data is retrieved.

• <aggregate_function>: The aggregate function to be applied (SUM, MAX, MIN, AVG, etc.) to aggregate data.

• <value_column>: The column whose values will be pivoted.

• <pivot_column>: The column that provides the values for pivoting.

• [pivot_value1], [pivot_value2], ..., [pivot_valueN]: Values used for pivoting.

Example: Consider a table named Sales with columns Year, Quarter, and Revenue. You want to pivot the data based on the Quarter.

SELECT Year, [Q1], [Q2], [Q3], [Q4] FROM

(SELECT Year, Quarter, Revenue FROM Sales) AS SourceTable

PIVOT ( SUM(Revenue) FOR Quarter IN ([Q1], [Q2], [Q3], [Q4]) )

AS PivotTable;

Unpivot in SQL

Unpivoting involves converting columns into rows. Here's the syntax for unpivoting in SQL:

SELECT <unpivoted_column>, <value> FROM

(SELECT <columns> FROM <table>) AS SourceTable

UNPIVOT ( <value> FOR <unpivoted_column> IN ([column1], [column2], ..., [columnN]) )

AS UnpivotTable;

Explanation:

• <unpivoted_column>: Column created to represent the original columns.

• <value>: The values corresponding to the original columns.

• <columns>: Columns to be unpivoted.

• <table>: The table from which data is retrieved.

• [column1], [column2], ..., [columnN]: Columns to be unpivoted.

Example: Assume a table named Sales with columns Year, Q1, Q2, Q3, and Q4 representing quarterly sales. You want to unpivot the data.

SELECT Year, Quarter, Revenue FROM (SELECT Year, Q1, Q2, Q3, Q4 FROM Sales) AS SourceTable UNPIVOT ( Revenue FOR Quarter IN ([Q1], [Q2], [Q3], [Q4]) ) AS UnpivotTable;

These examples illustrate the basic syntax and usage of pivot and unpivot operations in SQL to transform data in different ways based on the specific requirements of your data analysis or reporting needs.

User Handling

Creating a User

Syntax : CREATE USER username IDENTIFIED BY 'password';

Example: CREATE USER 'myuser' IDENTIFIED BY 'mypassword';

Granting Privileges to a User

Syntax: GRANT privilege_name ON database_name.table_name TO username;

Example: GRANT SELECT, INSERT ON mydatabase.mytable TO 'myuser';

Revoking Privileges from a User

Syntax: REVOKE privilege_name ON database_name.table_name FROM username;

Example: REVOKE SELECT, INSERT ON mydatabase.mytable FROM 'myuser';

Dropping a User

Syntax: DROP USER username;

Example: DROP USER 'myuser';

Changing User Password

Syntax: ALTER USER username IDENTIFIED BY 'new_password';

Example: ALTER USER 'myuser' IDENTIFIED BY 'newpassword';

Listing Users

Syntax: SELECT user FROM mysql.user;

Example: SELECT user FROM mysql.user;

Showing User Privileges

Syntax: SHOW GRANTS FOR username;

Example: SHOW GRANTS FOR 'myuser';

These commands may vary slightly between different SQL database management systems like MySQL, PostgreSQL, SQL Server, etc. Adjustments might be needed based on the specific system being used.

Windows Function

1. ROW_NUMBER()

• Syntax: ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column2)

• Example: SELECT ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num, name, salary FROM employees;

2. RANK()

• Syntax: RANK() OVER (PARTITION BY column1 ORDER BY column2)

• Example: SELECT RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank, name, salary FROM employees;

3. DENSE_RANK()

• Syntax: DENSE_RANK() OVER (PARTITION BY column1 ORDER BY column2)

• Example: SELECT DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_dense_rank, name, salary FROM employees;

4. NTILE()

• Syntax: NTILE(n) OVER (ORDER BY column)

• Example: SELECT NTILE(4) OVER (ORDER BY salary DESC) AS quartile, name, salary FROM employees;

5. LEAD()

• Syntax: LEAD(column, offset, default) OVER (ORDER BY column)

• Example: SELECT name, salary, LEAD(salary, 1, 0) OVER (ORDER BY salary DESC) AS next_salary FROM employees;

6. LAG()

• Syntax: LAG(column, offset, default) OVER (ORDER BY column)

• Example: SELECT name, salary, LAG(salary, 1, 0) OVER (ORDER BY salary DESC) AS previous_salary FROM employees;

7. SUM()

• Syntax: SUM(column) OVER (PARTITION BY column1 ORDER BY column2)

• Example: SELECT department_id, salary, SUM(salary) OVER (PARTITION BY department_id) AS dept_salary_total FROM employees;

8. AVG()

• Syntax: AVG(column) OVER (PARTITION BY column1 ORDER BY column2)

• Example: SELECT department_id, salary, AVG(salary) OVER (PARTITION BY department_id) AS dept_salary_avg FROM employees;

9. COUNT()

• Syntax: COUNT(column) OVER (PARTITION BY column1 ORDER BY column2)

• Example: SELECT department_id, COUNT(*) OVER (PARTITION BY department_id) AS dept_employee_count FROM employees;

10. FIRST_VALUE()

• Syntax: FIRST_VALUE(column) OVER (PARTITION BY column1 ORDER BY column2)

• Example: SELECT department_id, name, salary, FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary DESC) AS top_salary FROM employees;

11. LAST_VALUE()

• Syntax: LAST_VALUE(column) OVER (PARTITION BY column1 ORDER BY column2)

• Example: SELECT department_id, name, salary, LAST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_salary FROM employees;

12. PERCENT_RANK()

• Syntax: PERCENT_RANK() OVER (PARTITION BY column1 ORDER BY column2)

• Example: SELECT department_id, name, salary, PERCENT_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_percent_rank FROM employees;

13. CUME_DIST()

• Syntax: CUME_DIST() OVER (PARTITION BY column1 ORDER BY column2)

• Example: SELECT department_id, name, salary, CUME_DIST() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_cume_dist FROM employees;

Triggers

In SQL, triggers are special types of stored procedures that automatically execute in response to certain events on a particular table or view. The most common triggers are BEFORE and AFTER triggers, which execute before or after a data modification event (such as INSERT, UPDATE, or DELETE). Here's a list of triggers with their syntax and examples:

1. BEFORE INSERT Trigger

• Syntax:

  CREATE TRIGGER trigger_name BEFORE INSERT ON table_name

  FOR EACH ROW

  BEGIN

  -- Trigger action

  END;

• Example:

  CREATE TRIGGER before_insert_trigger BEFORE INSERT ON employees

  FOR EACH ROW

  BEGIN

  SET NEW.created_at = NOW();

  END;

  This trigger sets the created_at column to the current timestamp before inserting a new record into the employees table.

2. AFTER INSERT Trigger

• Syntax:

  CREATE TRIGGER trigger_name AFTER INSERT ON table_name

  FOR EACH ROW

  BEGIN

  -- Trigger action

  END;

• Example:

  CREATE TRIGGER after_insert_trigger AFTER INSERT ON orders

  FOR EACH ROW

  BEGIN

  UPDATE inventory SET quantity = quantity - NEW.quantity

  WHERE product_id = NEW.product_id;

  END;

  This trigger updates the inventory after a new order is inserted into the orders table by reducing the quantity of the ordered product in the inventory table.

3. BEFORE UPDATE Trigger

• Syntax:

  CREATE TRIGGER trigger_name BEFORE UPDATE ON table_name

  FOR EACH ROW

  BEGIN

  -- Trigger action

  END;

• Example:

  CREATE TRIGGER before_update_trigger BEFORE UPDATE ON products

  FOR EACH ROW

  BEGIN

  IF NEW.price < OLD.price THEN

  SET NEW.price = OLD.price; -- Reject price decrease

  END IF;

  END;

  This trigger prevents the price of a product from being decreased by reverting the price back to its original value if a decrease is attempted.

4. AFTER UPDATE Trigger

• Syntax:

  CREATE TRIGGER trigger_name AFTER UPDATE ON table_name

  FOR EACH ROW

  BEGIN

  -- Trigger action

  END;

• Example:

  CREATE TRIGGER after_update_trigger AFTER UPDATE ON customers

  FOR EACH ROW

  BEGIN

  INSERT INTO customer_logs (customer_id, action, timestamp) VALUES (NEW.customer_id, 'Updated information', NOW());

  END;

  This trigger logs customer updates by inserting a record into the customer_logs table after an update on the customers table.

5. BEFORE DELETE Trigger

• Syntax:

  CREATE TRIGGER trigger_name BEFORE DELETE ON table_name

  FOR EACH ROW

  BEGIN

  -- Trigger action

  END;

• Example:

  CREATE TRIGGER before_delete_trigger BEFORE DELETE ON users

  FOR EACH ROW

  BEGIN

  IF OLD.admin = 1 THEN

  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot delete admin user';

  END IF;

  END;

  This trigger prevents the deletion of an admin user from the users table by throwing an error.

6. AFTER DELETE Trigger

• Syntax:

  CREATE TRIGGER trigger_name AFTER DELETE ON table_name

  FOR EACH ROW

  BEGIN

  -- Trigger action

  END;

• Example:

  CREATE TRIGGER after_delete_trigger AFTER DELETE ON departments

  FOR EACH ROW

  BEGIN

  DELETE FROM employees WHERE department_id = OLD.department_id;

  END;

  This trigger removes all employees associated with a department after the department is deleted from the departments table.