SQL Notes PDF

# SQL (Structured Query Language) - SQL is a non-procedural language introduced by IBM in the 1970s. - It is used to communicate with a database. ## How SQL communicates with a database - **Diagram:** - User - SQL - DB - Create - Insert - Update - Delete - Select...

# SQL (Structured Query Language) - SQL is a non-procedural language introduced by IBM in the 1970s. - It is used to communicate with a database. ## How SQL communicates with a database - **Diagram:** - User - SQL - DB - Create - Insert - Update - Delete - Select - The user communicates with the database using SQL commands. - SQL can also be called as sequel of CLI language (Common Language Interface). - SQL can communicate with any RDBMS product such as Oracle, SQL Server and MySQL ## SQL is not Case Sensitive - We can write sal predefined queries or syntoxes in any case characters (either upper or lower). - Every SQL statement should end with a semicolon, but this is optional in SQL servers. ## SubLanguages of SQL 1. **DDL (Data Definition Language)** - Commands are used to define, modify and drop objects of the database from the SQL server. - **Create:** Creating a new database or new table in SQL server - **Syntax:** - `Create database < Database Name>;` - `Create table < Table Name> (<Column Name> <Data Type> [Size], <Column Name 2> <Data Type> [Size]);` - **Example:** - `Create database MYDB;` - `Create table Student (Sid Int, Sname char (10), Sfee Decimal (6, 2), AGE Tinyint);` - **sp-Help:** To view the structure of the table - **Syntax:** `SP-HELP <Table Name>;` - **Example:** `SP-HELP Student;` - sp-HELP is a predefined stored procedure. - **Alter:** To change or modify the structure of a table or database. - We can perform the following four operations on existing tables - **Alter Column:** To change data type or the size of the data type of a particular column. - **Syntax:** `Alter table <TableName> Alter Column <ColumnName> <NewDataType> [New Size];` - **Example:** `Alter table Student Alter Column Name varchar (50);` - **Alterr Add:** Adding a new column to an existing table. - **Syntax:** `Alter table <TableName> ADD <New Column Name> <DataType> [Size]; ` - **Example:** `Alter Table Student ADD address varchar (30)` - **Sp-Rename:** To change a column name or a table name in a database. - **Syntax to change a column name:** `Sp-Rename <TableName>.<Old Column Name> <New Column Name>;` - **Example:** `Sp-Rename Student.SName, "Student Names";` - **Syntax to change a table name:** `Sp-Rename <Old Table Name>, <New Table Name>;` - **Example:** `Sp-Rename Student, 'Studentdetails';` or `Sp-Rename Studentdetails, 'student'` - **ALTER-Drop:** Dropping a column from the table. - **Syntax:** `Alter table <TableName> Drop Column <ColumnName>;` - **Example:** `Alter table Student Drop column AGE` - **Truncate:** Deleting rows from the table, but not the structure of the table. - We can not delete a specific row using the truncate command because it doesn't support the 'where' clause condition. - **Syntax:** `Truncate table <TableName>;` - **Example:** `Trimcate table Student` - **Drop:** Dropping a table from a database permanently. - **Syntax:** `Drop table <Table Name>;` - **Example:** `Drop table student;` 2. **DML (Data Manipulation Language)** - Commands are used to change or manipulate data in a database table. - **Insert:** Inserting a new row into a table. - There are two methods to insert rows into a table. - **Implicit Method:** Inserting all the values for all columns into a table (without leaving any column). - **Syntax:** `Insert [into] <TableName> values (Value1, Value2, Value3...);` - **Example:** - `Create table student (STID int, SHome varchar (40), sfee decimal (6, 2), Age tinyint)` - `Insert into student values (101, 'SAI', 2500, 21)` or - `Insert Student Values (102, 'JAMES', 4500, 23)` - **Explicit Method:** Inserting values for required columns only (without leaving any column in the table). - **Syntax:** `Insert [INTO] <TableName> (Required Column Names) values (Value1, Value2...);` - **Example:** - `Insert [INTO] <TableName> (Required Column Nomes) values (103 'ALLEN')` - **To insert multiple rows into a table** - **Syntax for implicit:** `Insert [INTO] <TableName> Values (Row1 values), (Row2 values) -----` - **Example:** `Insert into Student values (104, 'Scott', 1800, 22), (105, 'Hard', 1000, 25)` - **Syntax for Explicit:** `Insert [INTO] <TableName> (Required Column Names) Values ((Row1 values), (Row2 values), ....)` - **Example:** `Insert student (STID) values (106), (107) (108)` - **Update:** Updating all rows' data in a table at a time or a specific row data in a table by using the 'where' condition. - **Syntax:** `Update <TableName> SET <ColumnName1> = <Value1>, <ColumnName2> = <Value2> [Where Condition];` - **Example:** - `Write a query to update employee job as HR, Salary as 14,000 @ whose employee numberr is 7788` - `update emp set Job = 'HR', Salary = 14000 Where EmpNO = 7788` - `Write a query to update all employee Commition as 500` - `updote Emp set comm = 500` - **Delete:** Deleting all rows from the table at a time or a specific row from the table by using the where clause condition. - **Syntax:** `Delete from <TableName> [Where<Condition>];` - **Example:** - `Write a query to delete employee from the table who are working in the job is Cleark.` - `Delete from Emp Where job = 'cleark'` - `Write a query to delete all emp details from the table` - `Delete from Emp` ## Difference between DELETE and TRUNCATE - **DELETE** - It is a DML operation. - It can delete a specific row from the table. - It supports the 'where' clause condition. - It is temporary data deletion. - We can restore deleted data by using rollback. - Execution Speed is slow. - **TRUNCATE** - It is a DDL operation. - It is not possible to delete a specific row. - It doesn't support the 'where' clause condition. - It is permanent data deletion. - We can not restore deleted data by using rollback. - Execution speed is fast. ## `ISNULL` Function - **Note:** In the above query example, the employee smith salary is $8000. - There is no commission so that `salary + Comm` is 8000 only but it returns `Null`. - To overcome this problem, we should use a predefined function in the SQL server called `IS NULL` function. - **Syntax:** `IS NULL (exp1, exp2) ` - It is a predefined function that is used to replace a user-defined value in place of `NULL`. - This function has the following two arguments: `exp1` and `exp2`. - If `exp1` is `NULL`, then it returns `exp2`. - If `exp1` is not `NULL`, then it returns `exp2` value. - **Examples:** - `Select IS NULL (NULL, 0) AS Result = 0` - `Select IS NULL (NULL, 100) AS Result = 100` - `Select IS NULL (0,100) AS Result = 0` - `Select IS NULL (50, 0) AS Result = 50` - **Example of using `IS NULL`:** - `select EName, Job, Salary, Comm, Salory + IS NULL (Comm, 0) AS total from Emp Where EName = 'Smith'` - **Output:** - `ENome` | `Job` | `Salary` | `Comm` | `Total` - `Smith` | `cleark` | `8000` | `NULL` | `8000` ## `LIKE` Operator - To perform a database operation (select, update, delete) on a specific character pattern. - We work with the `LIKE` operator by using the following wild card operators: - `%`: It represents the remaining group of characters after the selected character in the expression. - `_`: It represents a single character. - `[]`: It represents a set of characters. - `Syntax:` `Where <Column Name> Like '[< wildcard operator> <special character> <wildcard operator>]'` - **Example:** - Display employee who's home starts with `'S'` character: `Select * from Emp Where EName Like 'S%'` - Display employee who's employee name is having a second character as `'O'`: `select * from Emp Where EName Like `e0%` - Display employee who's name is having four characters: `select * from Emp Where Exame like `____`. - Display employee who's name contains `'I'`. `Select * from Emp Where Ename like `%I%`. - Display the list of employee who join in the year 1981. `Select * from Emp Where Hiredate Like '1981%'`. - Display list of employee who join in the month of feb. `Select * from Emp Where Hiredate Like '%_02_%`. ## `LIKE` operator with special characters - To display employee whose name is having `'@'` symbol. `select * from Emp Where Ename like `%@%`. - To display employee whose name is having `'#'` symbol. `Select * from Emp Where EName like `%%#%`. - To display employee whose name is having `'_'` (underscore) symbol: `Select * from Emp Where ErName. Like `%._%`. - **Note:** Generally when we use `%` symbol in `WHERE` condition along with the `like` operator. SQL server will treat `%` as wild card operators. But not the special characters. So to avoid this problem, we should use the special Keyword `ESCAPE`. - **Example:** `select * from EName Where EName Like '%%%" Escape '\` - To display employee whose name is having `%` symbol: `Select * from Emp Where EName like `% % % Escape '\`. - To display employee whose name starts with A, C, M, W: `Select * from Ename Where Ename like '[A, C, M, W]`. ## `NOT LIKE` Operator - Example: Write a query to display employee details whose name is not starting with `'S'` character. `Select * from EName Where Ename not like 'S%`. ## Set Operators - Set operators retrieve data from a single table or multiple tables vertically. - Set operators are: - `UNION` - `UNION ALL` - `INTERSECT` - `EXCEPT` - **UNION:** It returns all values from all sets without duplicates. - **UNION ALL:** It returns all values from all sets including duplicates. - **INTERSECT:** It returns common values. - **EXCEPT:** It returns uncommon values from the left-side set but not the right side. - **Example on set operators with a single table:** - `Select * from <TableName> [Where <Condition>] <set operator> Select * from <Table Nome > [Where <condition>]` - **Example on set operators with multiple tables:** - `Select * from <TobleNameD [Where <condition>] <set operator> Select * from < Table Name 2> [ Where <condition>]` - **UNION Example:** - `Select job from Emp Where Deptno=10 UNION Select job from Emp Where Deptno=20` - **Output:** - `Dept No`| `Job` - `10` | `Manager` - `10` | `President` - `10` | `Cleark` - `10` | `Manager` - `20` | `Cleark` - ` 20` | `Manager` - ` 20` | `Analyst` - ` 20` | `Cleark` - ` 20` | `Cleark` - **UNION ALL Example:** - `Select Job from Emp Wherre DeptHo = 10 UNION all Select Job from Emp Where DeptNo=20` - **INTERSECT Example:** - `select Job from Emp Where DeptNo=10 Intersect Select Job from Emp Where DeptNo=20` ## Aliases - **Column Level alias name:** - **Syntax:** `<ColumnName> [AS] <Column alias Name>` - **Example:** `DeptNo AS Dept (OR) DeptNo Dept` - **Table Level Alias Name:** - **Syntax:** `<TableName> [AS] <Table Alias Name>` - **Example:** `Dept AS D (OR) Dept D` - **Combined Column + Table Level Alias Name:** - **Syntax:** `Select <Column Name1> [As] <column Alias Name 1> <Column Name2> [AS] <Column Alias Name 2> ----- from <TableName> [As] <Table Alias Name>;` - **Example:** - `Select Deptno as x, DNome AS y, Loc As z from Dept As D` - `Select DeptNo x, DName y, Loc z from Dept D.` - **Diagram:** - Database - Table -> Dept - Dept No, Dname, Loc - X, X, X - Y, Y, Y - Z, Z, Z - Create alias name - Buffer - Dept No, DName, Loc - X, X, X - Y, Y, Y - Z, Z, Z - Display - X, Y, Z - X , X, X - Y, Y, Y - Z, Z, Z - **Note:** Whenever creating alias names on tables or columns internally, a database server creates a virtual copy of each alias name and stores it in buffer memory. ## IDENTITY (Seed, Increment) - It is a predefined method that is used to generate the identity value on a particular column in the table automatically. - By using `IDENTITY`, we provide autoincrement value facility on a table. - A database table should contain only one `IDENTITY` column. - **Syntax:** `IDENTITY (seed, Increment)` - **seed:** Represents starting value of identity (default value: 1). - **Increment:** Represents the incremental values between IDs (default value: 1). - `IDENTITY (seed, Increment) = IDENTITY (1, 1)` - **Example:** Create a table with an `IDENTITY` column using the default values - `Create table Testi (SNO Int IDENTITY, Name varchar(30));` - **Testing:** - `Insert Testi Values (1, 'A')` - Error - `Insert Testi values ('A')` - Allowed - `Insert Testi (SNO, Name) values (2,'B')` - Error - `Insert Testi (Name) values ('B')` - Allowed - **Table Output:** - `SNo` | `Name` - `1` | `A` - `2` | `B` - **Example of `IDENTITY` with user-defined values:** - `Create table Test2 (SNO Int identity (100,5) , Name varchar(30))` - **Testing:** - `Insert Test2 Volues ('A')` - Allowed - `Insert Test 2 (Name) values (B')` - Allowed - **Table output:** - `SNO` | `Name` - `100` | `A` - `105` | `B` - **Note:** In the above examples, the user can not insert values to the `IDENTITY` column by explicitly. - If we want to insert values to an `IDENTITY` column explicitly, we follow this syntax: - `Set identity insert <TableName> OFF/ON` - **OFF:** It is a default connection of `IDENTITY`. The user cannot insert values to the `IDENTITY` column by explicitly. - **ON:** The user can insert values to the `IDENTITY` column by explicitly. - **Examples:** - `Set identity-insert Test ON` - `Insert Test (SNO, Name) values (3, 'C')` - Allowed - `Set identity - insert Test OFF` - `Insert Test (SNO, Home) values (4,'D')` - Error ## Operators - Operators perform some operations on given operand values. - SQL Server supports the following operators: - **Assignment operator:** = - **Arithmetic operator:** + - * / - **Relational operator:** = <,>, <=, >=, !<, !> - **Logical operator:** AND, OR, NOT - **Set operator:** UNION, UNION ALL, INTERSECT, EXCEPT - **Special operator:** - Positive operator - `IN` - `BETWEEN` - `IS NULL` - `LIKE` - Negative operator - `NOT IN` - `NOT BETWEEN` - `IS NOT NULL` - `NOT LIKE` - **Example:** - **Assignment operator:** To assign a value to a variable or a attribute - <Column Name> <Assignment operator> <value> - **Example:** - `Select * from Emp Where EmpNo=7788` - In `T/SQL` Syntax: - `Declare @ <Variable Name> <DataType> [Size]` - `set @ <Variable Name> <Assignment operator> <value>` - **Example:** - `Declare @ x int` - `Set @ x = 1.01` - **Arithmetic operators:** To perform some mathematical calculations such as addition, subtraction, multiplication and division. - `<Column Name> <Arithmetic operator> <value>` - **Examples:** - `Write a query to display Employee Salaries after adding 1000` - `Select Salary, Salary + 1000 As Result from Emp ` - `Write a query to display employee name, job, Salary and annual Salary of the employee from the table` - `Ex: - Select Ename, Job, Salary, Salary *12 AS Annual Salory from Emp.` - `Write a query to update employee Salary with a increment of 10% of working with job is analyst.` - `Ex: Update Emp Set Salomy = Salory + Salory * 10/100 Where JOB = 'Analyst'` - `Write a query to update all employee salaries with an increment of 5%.` - `Ex: - update. Emps Set Salorry = Salary + Salary * 0.5` - **Relational operators:** Compares a specific column value with even user-defined conditions. - `Where <Column Name> <Relational operator> <Value>` - **Examples:** - `Write a query to display the list of employee who are joined before 1981?` - `Select * from emp where Hiredate.< 1981-01-01` - `Write a query to insert StudentId, Student Name and there 4 subjects marks find total, average of class of each student` ## Logical operators - Logical operators check more than one condition in the query. - These operators are: - `AND` - `OR` - `NOT` - **AND Operator:** - `Cond1` | `Cond2` | `Result` - `T` | `T` | `T` - `T` | `F` | `F` - `F` | `T` | `F` - `F` | `F` | `F` - It returns a value when a given all conditions are true in the query. - **Syntax:** `Where <Condition1> AND <Condition2> AND <Condition 3>` - **Example:** - `Write a query to display employee who are working in the job is cleark & Whos Name is 'Word'.` - `Select * from Emp Where Job = 'Cleark' AND Ename = 'Word',` - **OR Operator:** - `Cond1` | `Cond2` | `Result` - `T` | `T` | `T` - `T` | `F` | `T` - `F` | `T` | `T` - `F` | `F` | `F` - It returns a value if any one condition is true from the given group of conditions. - **Syntax:** `Where <condition1> OR <condition2> OR <Condition 3> OR -----` - **Example:** - `Write a query to display a list of employee who are working under the employee numbers are 7369, 7566, 7788.` - `Select * from Emp Where EmpNo=7369 OR EMPNO = 7566 OR EmpNo=7788 ` - **NOT Operator:** - It returns all the values except the given conditional values in the query. - **Syntax:** `Where NOT <Column Name> = <value> AND NOT <columnName> = <Vokee> AND -----` - **Example:** - `Write a query to delete the list of employee from the table who are not working under the job is clear of amolyst.` - `Delete from Emp Where NOT JOB = 'Clear' AND JOB = 'Analyst'` ## Special Operators - SQL server supports the following special operators: - **`IN` operator:** Comparing the group of values based on a single condition in the query. - **Syntax:** `Where <Column Name> In(<list of Volbes>)` - **Example:** `To display the list of employee. Who are working under the employee Numbers are 7369, 7566, 7788. ` - `Select * from Emp Where EmpNo In (7369, 7566, 7788)` - **`NOT IN` operator:** It returns the list of values except the given conditional values. - **Syntax:** `Where <Column Name> NOT In <list of Values>` - **Example:** `To delete list of employee from the table who are not working under the job is Salesman, Mamager, president. ` - `Delete from Emp Where JOB NOT In ('Salesman', 'Manager', 'president')` - **`BETWEEN` operator:** Works on a particular range of values. - **Rules:** - It returns all the values, including the source and destination values, from the given range. - It can be implemented along with the `AND` operator. - It always uses the lower values to higher values. - **Syntax:** `Where <ColumnName> Between <low Value> AND <High Value>` - **Example:** `To display employee who's employee. Solany between 10,000 & 47,000` - `select * from Emp Satang Where Salary between 10,000 AND 47,000` - `Select * from Emp Wherre (salary>= 10,000) AND ( Salary <= 47,000)` - `Between = (>= AND <=)` - `To Display the list of employee who are join in the year of 1981` - `Select * from Emp Where HireDate between '1981-01-01' AND '1981-12-31'` - **`NOT BETWEEN` operator:** Returns all the values, except the given range values. - **Syntax:** `Where <Column Name> NOT between <low value> AND <High Value>` - **Example:** `To Display list of employee who are not joined in the year of 1981` - `select * from Emp Where Hiredate NOT between '1981-01-01' AND '1981-12-31'` - **`IS NULL` operator:** Compares `Null` values in a table. - **Syntax:** `Where <Column Name> is NULL` - **Example:** `Write a query to display the list of employee who's Commission is not Null.` - `select * from Emp Where Comm is Null` - **`IS NOT NULL` operator:** Compares `Null` values in a table. - **Syntax:** `Where <Column Name> is not NULL` - **Example:** `Write a query to display the list of employee who's Commission is not Null.` - `select * from Emp Where Comm is not NULL` - **Working with `Null`** - `Null` is a unknown or undefined value in a database. - `Null` is not equal to zero or space. - Any arithmetic operation performed with `Null` returns `Null`. - `a + Null = Null` - `a - Null = Null` - `a * Null = Null` - `a / Null = Null` - **Example:** `Write a query to display employee.Name, job, Salorry, COMM & also salary + comm from the table who's employee.Name as Smith.` - `Select Ename, job, Salary, COMM, Salory + COMM AS total from Emp Where Exame = 'Smith'` - **Output:** - `Ename` | `Job` | `Salary` | `COMM` | `Total` - `Smith` | `cleark` | `8000` | `Null` | `Null` ## Functions - A function is a block of code that performs a specific task and returns a value. - SQL server supports two types of functions: - **Predefined functions:** - These are built-in functions that are provided by SQL server. - These functions can be used directly without defining them. - **Examples:** - `Number functions`: `AVG()`, `SUM()`, `MAX()`, `MIN()`, `COUNT()`, `ABS()`, `CEILING()`, `FLOOR()`, `POWER()`, `PI()`, `LOG()`, `LOG10()` - `Character / String functions`: - `LEN()`: Returns the length of a given string. - `ASCII()`: Returns the ASCII value of a character. - `CHAR()`: Returns the character corresponding to a given ASCII value. - `LOWER()`: Converts a string to lowercase. - `UPPER()`: Converts a string to uppercase. - `LTRIM()`: Removes leading spaces from a string. - `RTRIM()`: Removes trailing spaces from a string. - `REVERSE()`: Reverses the characters in a string. - `REPLICATE()`: Repeats a string a specified number of times. - `REPLACE()`: Replaces occurrences of a substring within a string. - `CONCAT()`: Concatenates multiple strings. - `SUBSTRING()`: Extracts a substring from a string. - `Date & Time Functions`: `GETDATE()`, `GETUTCDATE()`, `DATEADD()`, `DATEDIFF()`, `DATENAME()`, `DATEPART() ` - `Aggregative/Grouping Functions`: `SUM()`, `AVG()`, `MAX()`, `MIN()`, `COUNT()` - **User-defined functions:** - These functions are created by the user. - They are used to perform custom tasks. - User-defined functions are created by using the `CREATE FUNCTION` statement. - **Syntax:** `select <Function Name>(value/Expression)` - **Example:** - **Number functions:** - **`ABS()` function:** Converts negative sign values into positive sign values. - **Example:** - `Select ABS (-12) AS Result ` - Output: `12` - `Select EName, salary, Comm, ABS (Comm - Salary) As result from Emp As result from Emp.` - **`CEILING()` function:** Returns a value that is greater than the given expression. - **Example:** - `Select Ceiling (9-3)` - Output: `10` - `Select Ceiling (-9.8)` - Output: `-9` - **`FLOOR()` function:** Returns a value that is less than the given expression. - **Example:** - `Select floor (9.8)` - Output: `9` - `Select floor (-9.3)` - Output: `-10` - **`POWER()` function:** Returns the power of the given expression. - **Example:** - `select power (2,3)` - Output: `8` - **`PI()` function:** Returns the pi value - **Example:** - `select pic()` - Output: `3.14` - **`LOG()` function:** Returns the logarithmic value of the given expression. - `Select log (3) As Result` - Output: `1.0986` - **`LOG10()` function:** Returns the base 10 logarithmic value. - `Select log 10 (10) As result ` - Output: `1` - **Character / String functions:** - **`LEN()` function:** Returns the length of a string - **Example:** - `Select LEN ('HELLO')` - Output: `5` - `Select LEN ('WEL, COME')` - Output: `8` - **`ASCII()` function:** Returns the ASCII number for a given character. - **Example:** - `Select ASCII ('z') As Result` - Output: `90` - **`CHAR()` function:** Returns a character for a given ASCII value. - **Example:** - `Select CHAR (90) AS Result` - Output: `Z` - **`LOWER()` function:** Converts uppercase characters into lowercase characters. - **Example:** - `update Emp set Ename = LOWER (Ename) Where Job = 'clerk'` - `Select LOHER (HELLO) As Result ` - Output: `hello` - **`UPPER()` function:** Converts lowercase characters to uppercase characters. - **Example:** - `Select UPPER ('chello') As Result` - Output: `HELLO` - **`LTRIM()` function:** Trims the leading space of the given string expression. - **Example:** - `Select LTRIM ('SAI ) As result` - Output: `SAI` - **`RTRIM()` function:** Trims the trailing spaces from the given string expression. - **Example:** - `Select RTRIM ('SAI ) As Result` - Output: `SAI` - **`REVERSE()` function:** Reverses the characters in the given string. - **Example:** - `select Reverse ('SAI') AS Result` - Output: `IASA` - `select Enome, Reverse (Ename) from Emp` - **`REPLICATE()` function:** Repeats a string of characters a specified number of times. - **Example:** - `Select Replicate ('SAI', 5) AS Result` - Output: `SAI, SAI, SAI, SAI,SAI` - **`REPLACE()` function:** Replaces occurrences of a substring within a string. - **Syntax:** `REPLACE ( 'string','<old char>','<new char>')` - **Example:** - `select replace ("Jack & Jue", "J","BL') AS Result` - Output: `Black & Blue` - **`CONCAT()` function:** Concatenates multiple strings. - **Example:** - `select concat ("Good", "Morning") As Result` - Output: `Goodmoming` - **`SUBSTRING()` function:** Extracts a substring from a string. - **Syntax:** `Substring ('string', <starting position of char>,< length of char>)` - **Example:** - `Select Substring ('Welcome', 4,2) AS Result` - Output: `CO` - **Date and Time functions:** - **`GETDATE()` function**: Returns the current date and time information from the system. - **Example:** - `Select GetDate()` - Output: `Current Date & Time`. - **`GETUTCDATE()` function:** Returns the current universal date and time information. - **Example:** - `Select GETUTCDATE()` - **`DATEPART()` function:** Returns the specified interval from the given date expression. - **Example:** - `Select Dateport (DD, Getdate())` - Output: `2 date` - `Select boteport (HH, GEtdate())` - Output: `20 hr's` - **`DATENAME()` function:** Returns the name of a specified interval from the given date expression. - **Example:** - `select Datename (DD, GETDATE())` - Output: `friday` - `Select Datename (MM, GET Date())` - Output: `August` - **`DATEADD()` function:** Adds a number of intervals to the given date expression. - **Example:** - `Select Dateadd (DD, 20, GetDate())` - `Select Dateadd (MM, 10, GetDate ())` - **`DATEDIFF()` function:** Returns the number of intervals between two given date expressions. - **Example:** - `Select datediff (DD, '2018-08-02', '2019-08-02')` - Output: `365` - **Aggregative functions:** - **`SUM()` function:** It returns the sum of the given group of values in a column. - **Example**: - `Select sum (salary) from Emp` - Output: `715,000` (assuming the total salary in the `Emp` is 715