SQL Server is a database management system developed by Microsoft for storing and retrieving data as requested by other software applications.

Database:

A database is a collection of related data. For example, a university database includes information on students, courses, and faculty, while a bank database contains data on customers, accounts, loans, and employees.

Types of Databases:

There are two main types of databases:

1. OLTP (Online Transaction Processing) Databases: Used for storing and managing day-to-day transactional data, where operations like Create, Read, Update, and Delete (CRUD) are performed regularly to support business operations.

2. OLAP (Online Analytical Processing) Databases: Used for analyzing large datasets to gain insights and make strategic decisions. Organizations use OLAP databases to perform complex queries and aggregations that help understand business trends and performance.

Short definition: OLTP databases handle operational tasks, while OLAP databases support analytical tasks by processing and analyzing data.

Difference between OLTP and OLAP databases:

• Organizations use OLTP for storing day-to-day transactions and OLAP for analysis.

• OLTP systems are crucial for managing daily business operations, while OLAP systems are used to analyze business data for decision-making and planning.

• Day-to-day operations on a database include:

  • C - Create

  • R - Read

  • U - Update

  • D - Delete

DBMS (Database Management System):

• DBMS stands for Database Management System.

• It is a software used to create, manage, and manipulate databases.

• DBMS allows users to create, read, update, delete data.

• DBMS is an inteface between user and database.

• 

Evolution of DBMS:

Types of DBMS

1. HDBMS

2. NDBMS

3. RDBMS

4. ORDBMS

RDBMS (Relational DBMS):

• A Relational Database Management System (RDBMS) is a type of database management system that uses tables to store and manage data.

• Introduced by E.F. Codd, the relational model organizes data into tables, which have rows and columns.

• E.F. Codd introduced 12 rules, called Codd's rules, to define what a database management system needs to be a true relational database management system (RDBMS).

Codd's 12 Rules:

1. Information Rule: All data must be stored in tables, i.e., rows and columns.

2. Guaranteed Access Rule: Every piece of data should be easy to access without confusion.Every table must contain a primary key to uniquely identify the records.

3. Systematic Treatment of Null Values: Null values must be treated consistently and differently from actual data.

4. Dynamic Online Catalog Based on the Relational Model: The database catalog must be relational and accessible using SQL.

5. Comprehensive Data Sub-language Rule: The system must support at least one relational language with clear syntax and full functionality.

6. View Updating Rule: All views that can theoretically be updated must be updateable through the system.

7. High-Level Insert, Update, and Delete: The system must support set-based operations for data manipulation.

8. Physical Data Independence: Changes to how data is stored should not require changes to the application.

9. Logical Data Independence: Changes to table structures should not require changes to the application.

10. Integrity Independence: Integrity constraints must be definable in the relational language and stored in the catalog.

11. Distribution Independence: The system must work correctly regardless of data distribution.

12. Non-subversion Rule: Low-level access methods must not bypass integrity constraints.

COLUMN: A collection of values assigned to one field.

RDBMS Features

1. Easy to access and manipulate data (CRUD).

2. Less redundancy (duplication of data).

3. More security guarantees data quality.

4. Supports data sharing (data accessible by multiple users).

5. Supports data integrity, i.e., data quality.

6. Supports transactions (ACID properties).

   • Atomicity: Ensures all operations in a transaction either complete successfully or none do.

   • Consistency: Guarantees that transactions move the database from one valid state to another.

   • Isolation: Ensures that transactions do not affect each other.

   • Durability: Ensures that committed transactions are permanently saved, even if the system fails.

RDBMS Software (SQL Databases)

SQL databases store data in a structured and organized format.

RDBMS Software is commonly known as SQL Databases because they use SQL to handle database operations.

• ORACLE

  • Vendor: Oracle Corporation

• MySQL

  • Vendor: Oracle Corporation

• SQL SERVER

  • Vendor: Microsoft

• POSTGRESQL

  • Vendor: PostgreSQL Global Development Group

• RDS (Relational Database Service)

  • Vendor: Amazon Web Services (AWS)

NoSQL Databases

NoSQL databases store data in an unstructured format.

• MongoDB

• cassandra

ORDBMS (Object Relational Database Management System)

• Definition:

  • ORDBMS is a combination of RDBMS (Relational Database Management System) and OOP (Object-Oriented Programming).

  • It improves RDBMS by adding object-oriented features like reusability.

• Key Points:

  • Combination: ORDBMS = RDBMS + OOP (Reusability, Security)

  • Reusability: RDBMS doesn't support reusability, but ORDBMS does.

  • User-Defined Types (UDT): ORDBMS supports reusability through UDTs (user-defined types).

• Examples:

  Oracle, SQL Server, PostgreSQL .

What is SQL Serve?

\=> SQL Server is an RDBMS software from Microsoft that also supports ORDBMS features. It is used to create and manage databases.

Database: SQL Server, MongoDB, Oracal, MySql are database.

Database Development Life Cycle

1. Analyze.

   • Requirements gathering and feasibility study.

   • Conceptual design using data modeling techniques.

2. Design.

   • Table Design: Developed by database designers or architects.

   • Techniques Used:

     • ER Model (Entity-Relationship Model)

     • Normalization

3. Develop.

   Database is developed by Developer & DBA (Database Admin).

   Developer: Responsible for creating database structures and application components.

   DBA (Database Administrator): Oversees the installation, configuration, and maintenance of the database environment.

   It is developed using only RDBMS tools like SQL Server.

4. Test.

• The database is tested by the QA (Quality Assurance) team using manual testing and automation tools like Selenium.

5. Deploy / Implement.

• Copying the database from the development server to the production server is called deployment or implementation.

• After deployment, end users can use the database for day-to-day transactions.

6. Maintenance.

• Ongoing support and optimization of the database system.

Access: Read, Write, and Use:

SQL Server

• Definition: SQL Server is a RDBMS software developed by Microsoft that also supports ORDBMS features for creating and managing databases.

• Usage: It is utilized for both database development and administration, making it suitable for both developers and database administrators (DBAs).

Versions:

Client & Server Architecture:

Server :-

• Definition server side: A system where SQL Server is installed and running.

• Inside the server SQL server manages DB and Instance.

Server Components:

1. SQL Server:

   • Manages databases (DB) and instances.

2. Database (DB):

   • Created on the hard disk (HDD/SSD) and serves as permanent storage.

3. Instance:

   • Runs in RAM and acts as temporary storage for processing and caching data. The instance is created in RAM and serves as temporary storage.

4. The database is created on the hard disk and serves as permanent storage.

Client :-

It is also a system from where user can connect to server, submit request and receives request.

Client Tool (SSMS):

• SSMS: SQL Server Management system.

• SQL: Structure Query Language.

• SQL Server: Database.

SQL (Structured Query Language):

Language Used to Communicate with SQL Server

• Definition: Users talk to SQL Server by sending commands called queries.

• Query: A command or question sent to SQL Server to do something with the database.

History and Commonality

• Origin: SQL was first created by IBM and was originally called "SEQUEL" (Structured English Query Language). It was later renamed to SQL (Structured Query Language).

• Commonality: SQL is used by all relational database management systems (RDBMS).

SQL Sub-languages

SQL has different sub-languages based on the operations performed on the database:

1. DDL (Data Definition Language):

   • Defines and manages database structures.

   • Examples: CREATE, ALTER, DROP, truncate .

2. DML (Data Manipulation Language):

   • Manipulates data within the database.

   • Examples: INSERT, UPDATE, DELETE , merge .

3. DQL (Data Query Language):

   • Queries and retrieves data from the database.

   • Example: SELECT.

4. TCL (Transaction Control Language):

   • Manages transactions within the database.

   • Examples: COMMIT, ROLLBACK, SAVEPOINT.

5. DCL (Data Control Language):

   • Controls access to data within the database.

   • Examples: GRANT, REVOKE.

Data & Data Defination

Data refers to meaningful information stored in a database, while data definition involves specifying the structure and organization of this data.

---

Download :

Free version:

• Download SQL Server here (SQL Server 2022 Developer is a full-featured free edition, licensed for development and testing in a non-production environment).

• Download SQL Server Management Studio (SSMS) 20.2

If you want to download a specific version, go here:

• For SQL Server: https://www.microsoft.com/en-in/sql-server/sql-server-downloads

• For SSMS: https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver16

summary :-

• what is db ?

• what is dbms ?

• what is rdbms ?

• what is ordbms ?

• db development life cycle ?

---

Create a new database:

• Create a database: In the left-side Object Explorer, right-click on Databases, click on New Database, enter the database name, select the path where you want to create the database or leave it as it is, and press OK.

• Another way to create a database: In the left-side Object Explorer, inside the Object Explorer click on Databases, inside the Databases click on System Databases, inside the System Databases right-click on master, click on New Database.

  Write a query to create a new database.

    Syntax:
    CREATE DATABASE <database name>
  
    Example:
    CREATE DATABASE Test
  

• When you create the database, two files are created: a DATA file and a LOG file.

  DATA file(Stores): Extension is MDF (Master data file).

  LOG file(Stores commands/operations): Extension is LDF (Log data file).

• In the left-side Object Explorer, under Databases, find your newly created database. Right-click on your database and select New Query.

Like creating a database name is Test .

SQL queries are not case-sensitive.

Datatype in SQL Server:

• A datatype specifies what type of data allowed in a column.

• Amount of memory allocates for column.

CHAR(size):

• CHAR(size) allows character data up to 8000 characters.

• CHAR is recommended for fixed-length character columns.

• Example: Suppose you define a column NAME CHAR(10). If you store the name "SACHIN", the remaining characters will be padded with spaces: SACHIN (SACHIN followed by four spaces), resulting in memory waste.

• A good example of using CHAR is for storing gender. GENDER CHAR(1) will store either 'M' or 'F'.

Varchar(size):

• Varchar(size) allows character data up to 8000 characters.

• Varchar is recommended for variable-length fields.

• Syntax:VARCHAR(n) where n is max length.

• Example: Suppose you define a column NAME Varchar(10). If you store the name "SACHIN", only the six characters "SACHIN" will be stored without any extra spaces.

Varchar(max):

• Allows character data upto 2GB.

• Syntax:VARCHAR(MAX) .

NOTS:

CHAR, VARCHAR, and VARCHAR(MAX) can store standard ASCII characters (like letters a-z, A-Z, numbers 0-9, and basic special characters) and Extended ASCII characters (like accented letters and more special symbols).

However, they cannot store all characters from different languages (like Chinese or Arabic) because they do not fully support Unicode. For storing characters from many languages, you should use NCHAR, NVARCHAR, or NVARCHAR(MAX).

/*Example:-*/
panno char(10) 
vehno char(10) 
emailid varchar(20) 
pwd varchar(10)
about varchar(max)

Difference between CHAR(size), VARCHAR(size), and VARCHAR(MAX):

NCHAR / NVARCHAR / NVARCHAR(MAX):

Allows unicode chars (65536 chars) that includes all ascii chars and chars belongs to different languages.

Difference between NCHAR(size), NVARCHAR(size), and NVARCHAR(MAX):

INTEGER:

• Allows numbers without decimal (integers).

Differences betweenTINYINT,SMALLINT,INTandBIGINTin SQL Server

Example: AGE TINYINT and EMPID SMALLINT .

FLOAT:

Numeric(p):

• Allows numbers without decimal upto 38 digits.

• Example:

    empid NUMERIC(4) /*4 is the number of digits
    10 - ok
    100 - ok
    1000 - ok
    10000 - not ok */
    phone NUMBERIC(10)
    aadharno Numeric(12)
  

Numeric(p,s) / Decimal(p,s):

• Allows numbers with decimals (float).

• p => precision => total number of digits allowed.

• s => scale => number of digits allowed after the decimal.

• Example:-

    salary Numeric(7,2);--Total number of digit is 7
    salary Decimal(7,2);
    /*5000 - ok
    5000.55 - ok
    50000.55 - ok
    500000.55 - not ok
    5000.5689 - ok because roundof 5000.5689n convert to 5000.57 */
  

• No difference between Decimal(p,s) and Numeric(p,s).

  ---

Currency:

• Currency types are used for fields related to money.

• Example:

    salary SMALLMONEY /*add when creating table*/
    balance MONEY /*add when creating table*/
  

Differences betweenSMALLMONEYandMONEYin SQL Server:

DATA:

• DATE => allows only dates

• TIME => allows only times

• DATETIME => allows both date and time

The default date format in SQL Server is yyyy-mm-dd.

The default time format is hh:mi:ss.

Example:

dob DATE /*add when creating table*/ /*2003-03-10 is formate*/
login TIME /*add when creating table*/ /*9:30:00 is formate*/
signup DATETIME /*add when creating table*/ /*2023-09-13 10:00:00 is formate*/

---

Creating Tables in SQL Server:

• Right-click on the database and click on New query .

Syntax:

CREATE TABLE <TableName>(
    field1 datatype(size),
    field2 datatype(size),
    ---------------------,
    ---------------------
);

Example:

This example is only to show how to use all data types in a table.

--Create emp database using: CREATE DATABASE emp
CREATE TABLE new_emp
(
    Gender CHAR(1),
    FullName VARCHAR(100),
    AboutUs VARCHAR(MAX),
    FirstName NCHAR(50),
    LastName NCHAR(50),
    ProductName NVARCHAR(100),
    AboutProduct NVARCHAR(MAX),
    AgeGroup TINYINT,
    QuantityOnHand SMALLINT,
    EmployeeID INT,
    TransactionID BIGINT,
    MonthlySalary NUMERIC(4, 2),
    Incentive SMALLMONEY,
    Package MONEY,
    JoiningDate DATE,
    OfficeComingTime TIME,
    DOB DATETIME
);

Rules:

1. The name must start with an alphabet.

2. The name cannot contain spaces or special characters, but can include _, #, and $.

3. The name can be up to 128 characters long.

4. A table can have up to 1024 columns.

5. The number of rows is unlimited.

Examples:

• 123emp - Invalid (starts with a number)

• emp 123 - Invalid (contains a space)

• emp*123 - Invalid (contains a special character *)

• emp_123 - Valid (follows all rules)

CREATE TABLE emp_123
(
    EmpID INT,
    EmpName VARCHAR(10),
    Job VARCHAR(10),
    EmpSalary MONEY,
    HireDate DATE,
    Age TINYINT,
    Gender CHAR(1)
);

The above command created the table structure, definition, and metadata, which includes columns, data types, and sizes.

SP_HELPcommand:

• SP_HELP: Stored Procedure for Table Structure.

• Definition: SP_HELP is a system stored procedure that provides information about the structure of a specified table, including its columns, data types, and other relevant details.

• Syntax: SP_HELP <tablename>

• Example: SP_HELP emp

• Output Example: When you run SP_HELP emp, you might see an output like:

Column Name    Data Type      Length
-------------------------------------
EMPID          tinyint       1
ENAME          varchar       10
JOB            varchar       10
SAL            smallmoney    4
HIREDATE       date          3
AGE            tinyint       1
GENDER         char          1

Explanation of Output:

• Column Name: The name of each column in the table.

• Data Type: The data type of each column (e.g., tinyint, varchar, smallmoney, etc.).

• Length: The maximum storage size for that column (e.g., the length of varchar types).

Inserting Data into the Table:

• The "INSERT" command is used to add data to a table.

• We can insert a single row or multiple rows.

• To insert data into our table, we use the INSERT INTO statement followed by the table name and the values we want to add.

Inserting Single Row Data into the table:

Syntax:

INSERT INTO <tabname> VALUES(v1,v2,v3,-,-,-,-,-);

Example:

INSERT INTO emp VALUES (1, 'John Doe', 'Manager', 50000, '2023-01-15', 35, 'M');

Inserting Multiple Row Data into the table:

Syntax:

INSERT INTO <tabname> VALUES(v1,v2,v3,-,-,-,-,-), (v1,v2,v3,-,-,-,-,-);

Example:

INSERT INTO emp VALUES (1, 'John Doe', 'Manager', 50000, '2023-01-15', 35, 'M'),
                   (2, 'Dev Doe', 'Sub Manager', 30000, '2023-01-15', 35, 'M');

Inserting Null Value:

A null means blank or empty, and it is not equal to 0 or space.

Nulls can be inserted in two ways.

By Explicit:

INSERT INTO emp VALUES(104,'ravi',null,null,'2018-02-12',25,'m');

By Implicit:

INSERT INTO emp(empid,ename,hiredate,age,gender) 
VALUES(105,'sindhu',getdate(),30,'f');

Remaining two fields job, sal are filled with nulls.

Operators in SQL Server:

• Arithmetic Operators:+ , - , * , / , % .

• Relational Operators:> , >= , < , <= , = , <> .

• Logical Operators:AND ,OR , NOT

• Special Operators:BETWEEN , IN ,LIKE ,IS ,ANY ,ALL ,EXISTS ,PIVOT .

• Set Operators:UNION ,UNION ALL ,INTERSECT ,EXCEPT .

Displaying Data:

The SELECT command is used to display data from a table.

• We can display all rows and all columns.

• We can display specific rows and columns.

Syntax:

SELECT columns FROM tabname; -- Use to select the particular columns  
--or
SELECT * FROM tabname; -- Use to select the all columns

• SQL is like English.

• Queries are like sentences.

• Clauses are like words.

Examples:

Display all the data from the EMP table.

SELECT * FROM EMP;

• * means all columns.

Display employee names and salaries.

SELECT ENAME, SAL FROM EMP;

EXAMPLES:

Create database name is SawMill:

CREATE DATABASE SawMill;

Create Table name is Emp:

CREATE TABLE Emp
(
    Id SMALLINT,
    FullName VARCHAR(100),
    Qualification VARCHAR(50),
    Gender CHAR(1),
    Salary NUMERIC(5,2),
    Discription VARCHAR(MAX)
);

Show Table Structure:

SP_HELP Emp;

Insert Single Row Data into Table:

INSERT INTO Emp VALUES 
(101, 'Mritunjay Kumar', 'BCA', 'M', 500.75, 'Hello');

Insert Multiple Row Data into Table:

INSERT INTO Emp VALUES 
(102, 'Mritunjay Kumar', 'BCA', 'M', 500.75, 'Hello'),
(103, 'Awnish Kumar', 'BCA', 'M', 300.75, 'Hello');

Insert Null value in Table:

INSERT INTO Emp VALUES 
(104, 'Amit Kumar', 'BCA', 'M', Null, Null);
/*OR*/
INSERT INTO Emp(Id, FullName, Qualification, Gender) VALUES 
(105, 'Amit Kumar', 'BCA', 'M');

Display all the data:

SELECT * FROM Emp;

Display particular data:

SELECT FullName, Qualification, Salary  FROM Emp;

---

WHERE Clause:

• Where clause is used to get specific rows from table based on a condition.

• The WHERE clause is used to filter records that match certain conditions. It helps to get specific rows from a table based on a given condition.

• Syntax:

    SELECT columns FROM tabname WHERE <condition>;
  

• Conditions: The condition typically follows the format.

    COLNAME OP VALUE
  

• COLNAME: The column name to apply the condition to.

• OP: A relational operator (e.g., >, >=, <, <=, =, <>).

• VALUE: The value to compare against.

• Examples:

    -- Display employee details whose empid = 103 ?
    SELECT *
    FROM EMP
    WHERE EMPID = 103;
    -- Or
    -- Display employee details whose name = ravi ? 
    SELECT *
    FROM EMP
    WHERE ENAME = 'ravi';
    -- Or
    -- Display employee details earning more than 5000 ?
    SELECT *
    FROM EMP
    WHERE SAL > 5000;
    --Or
    -- Employees joined after 2020 ?
    SELECT *
    FROM EMP
    WHERE HIREDATE > '2020-12-31';
    --Or
    -- Employees joined befor 2020 ?
    SELECT *
    FROM EMP
    WHERE HIREDATE < '2020-12-31';
  

WHERE Clause Compound Condition:

• A compound condition involves multiple conditions combined using AND and OR operators.

AND Operator:

• Both conditions must be true for the result to be true.

• Truth table for AND:

    codecond1  | cond2  | RESULT
    ------------------------
    T      | T      | T
    T      | F      | F
    F      | T      | F
    F      | F      | F
  

OR Operator:

• At least one condition must be true for the result to be true.

• Truth table for OR:

    codecond1  | cond2  | RESULT
    ------------------------
    T      | T      | T
    T      | F      | T
    F      | T      | T
    F      | F      | F
  

Examples of Compound Conditions:

Example 01:-

-- Employees working as clerk or manager:
SELECT *
FROM EMP
WHERE JOB = 'CLERK' OR JOB = 'MANAGER';
-- Or
-- Employees whose id is 100 or 103:
SELECT *
FROM EMP
WHERE EMPID = 100 OR EMPID = 103;
-- Or
-- Display male employees older than 30:
SELECT *
FROM EMP
WHERE GENDER = 'M' AND AGE > 30;
-- Or
-- Employees who joined in the year 2020:
SELECT *
FROM EMP
WHERE HIREDATE >= '2020-01-01' AND HIREDATE <= '2020-12-31';
-- Or
-- Employees earning more than 5000 and less than 10000:
SELECT *
FROM EMP
WHERE SAL > 5000 AND SAL < 10000;

Example 02:-

-- Table structure
CREATE TABLE STUDENT
(
    SID INT,
    SNAME VARCHAR(10),
    S1 TINYINT,
    S2 TINYINT,
    S3 TINYINT
);

-- Insert sample data
INSERT INTO STUDENT VALUES (1, 'A', 80, 90, 70), (2, 'B', 30, 60, 50);

-- List of students who passed all subjects
SELECT *
FROM STUDENT
WHERE S1 >= 35 AND S2 >= 35 AND S3 >= 35;

-- List of students who failed in at least one subject
SELECT *
FROM STUDENT
WHERE S1 < 35 OR S2 < 35 OR S3 < 35;

IN operator :

Use the IN operator for list comparison. Use the IN operator for "=" comparison with multiple values.

The IN operator is used for list comparisons, allowing for a cleaner and more readable way to check if a value matches any value in a list.

Usage:

• Invalid syntax using= for multiple values:

    WHERE COLNAME = V1, V2, V3, -- => INVALID
  

• Valid syntax usingIN for multiple values:

    WHERE COLNAME IN (V1, V2, V3, ...) -- => VALID
  

Examples of Using the IN Operator:

1. Employees whose id is 100, 103, or 105:

    SELECT * FROM EMP
    WHERE EMPID IN (100, 103, 105);
   

2. Employees working as a clerk, manager, or analyst:

    SELECT * FROM EMP
    WHERE JOB IN ('CLERK', 'MANAGER', 'ANALYST');
   

3. Employees not working as a clerk or manager:

    SELECT * FROM EMP
    WHERE JOB NOT IN ('CLERK', 'MANAGER');
   

Using the IN operator simplifies the query and makes it easier to read and maintain.

BETWEEN Operator in SQL:

The BETWEEN operator is used for range comparisons, allowing you to select values within a given range.

Usage:

WHERE COLNAME BETWEEN V1 AND V2

Examples of Using the BETWEEN Operator:

1. Employees earning between 5000 and 10000:

SELECT * FROM EMP
WHERE SAL BETWEEN 5000 AND 10000;

2. What happens when you useBETWEEN with the range in reverse?

    SELECT *
    FROM EMP
    WHERE HIREDATE NOT BETWEEN '2020-01-01' AND '2020-12-31';
   

3. Question:

   What happens when you useBETWEEN with the range in reverse?

    SELECT *
    FROM EMP
    WHERE SAL BETWEEN 10000 AND 5000;
   

   Options:

   A: ERROR

   B: RETURNS ROWS

   C: RETURNS NO ROWS

   D: NONE

   Answer: The correct answer is C: RETURNS NO ROWS, because BETWEEN 10000 AND 5000 is logically equivalent to (SAL >= 10000 AND SAL <= 5000), which will not match any rows.

4. Correct usage for employees earning between 5000 and 10000:

    WHERE SAL BETWEEN 5000 AND 10000 -- (SAL >= 5000 AND SAL <= 10000)
    WHERE SAL BETWEEN 10000 AND 5000 --(SAL>=10000 AND SAL<=5000)
   

5. Employees list working as clerk or manager, earning between 5000 and 10000, joined in 2023, and gender must be male:

    SELECT *
    FROM EMP
    WHERE JOB IN ('CLERK', 'MANAGER')
      AND SAL BETWEEN 5000 AND 10000
      AND HIREDATE BETWEEN '2023-01-01' AND '2023-12-31'
      AND GENDER = 'M';
   

6. List of Samsung and Realme mobile phones priced between 10000 and 20000:

    SELECT *
    FROM PRODUCTS
    WHERE BRAND IN ('SAMSUNG', 'REALME')
      AND CATEGORY = 'MOBILES'
      AND PRICE BETWEEN 10000 AND 20000;
   

Note:

• Use the BETWEEN operator with the lower value first and the upper value second to ensure correct results.

LIKE Operator in SQL:

The LIKE operator is used for pattern matching in SQL. It allows you to search for a specified pattern in a column.

Wildcard Characters:

• % : Represents zero or more characters

• _ : Represents a single character

Examples of Using the LIKE Operator:

1. Employees whose name starts with 's':

    SELECT *
    FROM EMP
    WHERE ENAME LIKE 's%';
   

2. Employees whose names end with 'd':

    SELECT *
    FROM EMP
    WHERE ENAME LIKE '%d';
   

3. Employees whose name contains 'a':

    SELECT *
    FROM EMP
    WHERE ENAME LIKE '%a%';
   

4. Employees where 'a' is the 2nd character in their name:

    SELECT *
    FROM EMP
    WHERE ENAME LIKE '_a%';
   

5. 'a' is the 3rd character from the last:

    SELECT *
    FROM EMP
    WHERE ENAME LIKE '%a__';
   

6. Names containing exactly 4 characters:

    SELECT *
    FROM EMP
    WHERE ENAME LIKE '____';
   

7. Names starting with 'a', 'r', or 'v':

    SELECT *
    FROM EMP
    WHERE ENAME LIKE '[arv]%';
   

8. Names starting between 'a' and 'p':

    SELECT *
    FROM EMP
    WHERE ENAME LIKE '[a-p]%';
   

9. Employees who joined in October (yyyy-mm-dd):

    SELECT *
    FROM EMP
    WHERE HIREDATE LIKE '_____10___';
   

10. Employees who joined in the year 2020:

    SELECT *
    FROM EMP
    WHERE HIREDATE LIKE '2020%';
    

11. Names containing an underscore (_):

    SELECT *
    FROM CUST
    WHERE CNAME LIKE '%_%';
    

12. Names containing a literal underscore (_), using escape character:

    SELECT *
    FROM CUST
    WHERE CNAME LIKE '%\_%' ESCAPE '\';
    

13. Names containing a percent sign (%), using escape character:

    SELECT *
    FROM CUST
    WHERE CNAME LIKE '%\%%' ESCAPE '\';
    

14. Names containing two underscores (__), using escape character:

    SELECT *
    FROM CUST
    WHERE CNAME LIKE '%\_%\_%' ESCAPE '\';
    

IS Operato:

The IS operator is used to compare a column's value with NULL. To check for NULL values, use IS NULL and IS NOT NULL.

1. To find rows where a column has no value (NULL), use IS NULL.

   To find employees who are not earning a salary:

    SELECT * FROM EMP
    WHERE SAL IS NULL;
   

2. To find rows where a column has a value (is not NULL), use IS NOT NULL.

   To find employees who are earning a salary:

    SELECT * FROM EMP
    WHERE SAL IS NOT NULL;
   

Practice questions 01:

Q1. Create Database and Emp table.

CREATE DATABASE Tcs;

Q2. Insert this data (101, 'John', 'Manager', 75000, '2020-01-15', 45, 'M').

Q3. Insert this data (102, 'Jane', 'Analyst', 60000, '2019-03-12', 34, 'F'), (103, 'Mike', 'Clerk', 35000, '2018-07-23', 28, 'M')

Q4. Insert this data also:

(104, 'Linda', 'Manager', 85000, '2021-05-01', 39, 'F'),
(105, 'James', 'Analyst', 62000, '2017-09-30', 29, 'M'),
(106, 'Emily', 'Clerk', 36000, '2020-11-18', 26, 'F'),
(107, 'Chris', 'Analyst', 64000, '2019-12-25', 31, 'M'),
(108, 'Anna', 'Manager', 82000, '2022-01-11', 42, 'F')

Q5. Display all employee details.

SELECT FROM EMP;

Q6. List the names and job titles of all employees.

SELECT ENAME, JOB FROM EMP;

Q7. Find the employees who are managers.

SELECT FROM EMP WHERE JOB = 'Manager';

Q8. Show the details of employees earning more than 60,000.

SELECT FROM EMP WHERE SAL > 60000;

Q9. Retrieve the names and salaries of employees whose salary is between 35,000 and 75,000.

SELECT ENAME, SAL FROM EMP WHERE SAL BETWEEN 35000 AND 75000;

Q10. Find the names of employees who joined after January 1, 2020.

SELECT ENAME FROM EMP WHERE HIREDATE > '2020-01-01';

Q11. List the details of male employees.

SELECT FROM EMP WHERE GENDER = 'M';

Q12. Find the employees whose names start with 'J'.

SELECT FROM EMP WHERE ENAME LIKE 'J%';

Q13. Show the details of employees who are either Analysts or Clerks.

SELECT FROM EMP WHERE JOB IN ('Analyst', 'Clerk');

Q14. List the names and ages of employees who are older than 30.

SELECT ENAME, AGE FROM EMP WHERE AGE > 30;

Q15. Find the employees who were hired in the year 2020.

SELECT FROM EMP WHERE HIREDATE BETWEEN '2020-01-01' AND '2020-12-31';

Q16. Display the details of employees earning exactly 62,000.

SELECT FROM EMP WHERE SAL = 62000;

Q17. Find the female employees working as Managers.

SELECT FROM EMP WHERE GENDER = 'F' AND JOB = 'Manager';

Q18. List the names of employees who have 'a' as the second character in their names.

SELECT ENAME FROM EMP WHERE ENAME LIKE '_a%';

Q19. Show the details of employees who are earning more than 35,000 but less than 65,000.

SELECT FROM EMP WHERE SAL > 35000 AND SAL < 65000;

Q20. Retrieve the details of employees who joined before July 1, 2020.

SELECT FROM EMP WHERE HIREDATE < '2020-07-01';

Q21. Find the employees whose job title is either 'Manager' or 'Analyst' and their age is greater than 30.

SELECT FROM EMP WHERE JOB IN ('Manager', 'Analyst') AND AGE > 30;

Q21. List the employees' names and hire dates who were hired in the month of December.

SELECT ENAME, HIREDATE FROM EMP WHERE HIREDATE LIKE '-12-%';

Q22. Show the details of employees whose salary is not between 35,000 and 70,000.

SELECT FROM EMP WHERE SAL NOT BETWEEN 35000 AND 70000;

Q22. Find the employees who were hired in 2021 or 2022.

SELECT FROM EMP WHERE HIREDATE LIKE '2021%' OR HIREDATE LIKE '2022%';

Q23. Find the employees whose names contain at least one digit.

SELECT FROM EMP WHERE ENAME LIKE '%[0-9]%';

Q24. List the employees whose job title is 'Analyst' and their age is not between 30 and 40.

SELECT FROM EMP WHERE JOB = 'Analyst' AND (AGE < 30 OR AGE > 40);

Q23. Show the details of employees who were hired in the second quarter of any year (April to June).

SELECT * FROM EMP WHERE HIREDATE LIKE '-04-%' OR HIREDATE LIKE '-05-%' OR HIREDATE LIKE '-06-%';

Q24. Find the employees whose names start with a vowel (A, E, I, O, U).

SELECT FROM EMP WHERE ENAME LIKE '[AEIOU]%';

Q25. Show the details of employees who joined in either January, February, or March of any year.

SELECT FROM EMP WHERE HIREDATE LIKE '-01-%' OR HIREDATE LIKE '-02-%' OR HIREDATE LIKE '__-03-%';

Q26. Find the employees whose job titles do not start with 'A' or 'C'.

SELECT FROM EMP WHERE JOB NOT LIKE 'A%' AND JOB NOT LIKE 'C%';

Q27. Find the employees who not earning Salary?

Select from emp where Sal is NULL;

Q28. Find the employees who earning Salary?

Select * from emp where Sal IS NOT NULL;

ALIAS:

Alias means temporary name another name or alternative name. Used to change column heading.

Syntax:

SELECT column_name AS alias_name FROM table_name;

• column_name: The original name of the column or expression.

• alias_name: The new name for the column in the query results.

1. Displaying Employee Names and Annual Salaries:

    SELECT ENAME, SAL * 12 AS ANNSAL FROM EMP;
   

2. Aliases can be enclosed in double quotes if they contain spaces or special characters.

    SELECT ENAME, SAL * 12 AS "ANNUAL SAL" FROM EMP;
   

3. You can use the calculated column in theWHEREclause for filtering:

    SELECT *, SAL * 12 AS ANNSAL FROM EMP
    WHERE SAL * 12 > 60000;
   

4. Calculating and Displaying Multiple Columns:

    SELECT ENAME, SAL,
           SAL * 0.2 AS HRA,   -- House Rent Allowance (20% of SAL)
           SAL * 0.3 AS DA,    -- Dearness Allowance (30% of SAL)
           SAL * 0.1 AS TAX,   -- Tax (10% of SAL)
           SAL + (SAL * 0.2) + (SAL * 0.3) - (SAL * 0.1) AS TOTSAL -- Total Salary
    FROM EMP;
   

5. Handling NULL Values with NVL Function:

   NVL(COMM, 0) replaces NULL with 0.

    SELECT ENAME, SAL, COMM, 
    SAL + NVL(COMM, 0) AS TOTSAL
    FROM EMP;
   

   Use NVL or equivalent function to handle NULL values in calculations.

6. Displaying Total and Average Scores:

    SELECT SID, S1 + S2 + S3 AS TOTAL, (S1 + S2 + S3) / 3 AS AVG
    FROM STUDENT;
   

   | SNO | SNAME | S1 | S2 | S3 | | --- | --- | --- | --- | --- | | 1 | A | 80 | 90 | 70 | | 2 | B | 30 | 60 | 50 |

ORDER BY Clause

The ORDER BY clause is used to sort the table based on one or more columns, either in ascending (ASC) or descending (DESC) order. By default, the sorting order is ascending if not specified.

Syntax:

SELECT columns
FROM table_name
[WHERE condition]
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

Example:

1. Arranging Employees by Name in Ascending Order:

    SELECT * FROM EMP
    ORDER BY ENAME ASC;
   

2. Arranging Employees by Salary in Descending Order:

    SELECT * FROM EMP
    ORDER BY SAL DESC;
   

3. Arranging Employees by Department (Ascending) and Within Department by Salary (Descending):

    SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP
    ORDER BY DEPTNO ASC, SAL DESC;
   

   Breakdown of Sorting Process:

   • First Level of Sorting (DEPTNO ASC): The database will first sort all employees by the DEPTNO column in ascending order.

   • Second Level of Sorting (SAL DESC): Within each department (group of rows with the same DEPTNO), the database will then sort the rows by the SAL column in descending order.

Example Data Before Sorting

Example Data After Sorting

4. Arranging Employees by Department and Within Department by Hire Date in Ascending Order:

    SELECT EMPNO, ENAME, HIREDATE, DEPTNO FROM EMP
    ORDER BY DEPTNO ASC, HIREDATE ASC;
   

5. Arranging Students by Average Score in Descending Order, then by Marks in M and P:

    SELECT SNO, SNAME, M, P, C, (M + P + C) / 3 AS AVG
    FROM STUDENT
    ORDER BY AVG DESC, M DESC, P DESC;
   

   | SNO | SNAME | M | P | C | AVG | | --- | --- | --- | --- | --- | --- | | 3 | C | 90 | 80 | 70 | 80 | | 4 | D | 90 | 70 | 80 | 80 | | 1 | A | 80 | 90 | 70 | 80 | | 2 | B | 60 | 70 | 50 | 60 |

6. Arranging Employees Working as Clerk or Manager by Salary in Descending Order:

    SELECT EMPNO, ENAME, JOB, SAL FROM EMP
    WHERE JOB IN ('CLERK', 'MANAGER')
    ORDER BY SAL DESC;
   

NOTES:

• The ORDER BY clause can include multiple columns for sorting.

• Sorting in SQL is case-sensitive and may vary depending on the database system's collation settings.

Order of execution:

• Aliases cannot be used in the WHERE clause because the WHERE clause is executed before SELECT.

• Aliases can be used in the ORDER BY clause because the ORDER BY clause is executed after SELECT.

DISTINCT Clause:

The DISTINCT clause is used in a SELECT statement to remove duplicate rows from the result set. It ensures that each returned row is unique based on the columns specified in the query.

Syntax:

SELECT DISTINCT column1, column2, ...
FROM table_name;

Example:

• Selecting Distinct Job Titles from the Employee Table:

    SELECT DISTINCT JOB FROM EMP;
  

  | JOB | | --- | | ANALYST | | CLERK | | MANAGER | | PRESIDENT | | SALESMAN |

• Selecting Distinct Department Numbers from the Employee Table:

    SELECT DISTINCT DEPTNO FROM EMP;
  

• Selecting Distinct Combinations of Department Number and Job Title

    SELECT DISTINCT DEPTNO, JOB FROM EMP;
  

  1. | DEPTNO | JOB | | --- | --- | | 10 | CLERK | | 10 | MANAGER | | 10 | PRESIDENT | | 20 | ANALYST | | 20 | CLERK | | 20 | MANAGER | | 30 | CLERK | | 30 | MANAGER | | 30 | SALESMAN |

Notes

• The DISTINCT keyword applies to all columns in the SELECT statement. It treats each unique combination of values in the specified columns as a separate entry.

• Using DISTINCT can be slow on large datasets because the database has to sort and compare rows to remove duplicates.

TOP clause:

Use to fetch the top N records based on a specific ordering.

Syntax:

SELECT TOP n * 
FROM table_name
ORDER BY column_name;

• n: The number of rows to return.

• column_name: The column used to order the data.

Example:

• Display the First 3 Rows from the Employee Table:

    SELECT TOP 3 * FROM EMP;
  

  • This query returns the first 3 rows from the EMP table based on the default row order.

• Display the Top 3 Highest Paid Employees:

    SELECT TOP 3 * FROM EMP
    ORDER BY SAL DESC;
  

  • This query returns the top 3 employees with the highest salaries, sorted in descending order.

• Display the Top 3 Employees Based on Experience:

    SELECT TOP 3 * FROM EMP
    ORDER BY HIREDATE ASC;
  

  • This query returns the top 3 employees, sorted by the hire date in ascending order.

• Display the Top 3 Maximum Salaries:

    SELECT DISTINCT TOP 4 SAL FROM EMP
    ORDER BY SAL DESC;
  

Notes

• The TOP clause is specific to certain SQL dialects like SQL Server and may not be supported or may have different syntax in other databases. For example, in MySQL, you would use LIMIT instead, and in Oracle, you would use ROWNUM.

• When used with ORDER BY, the TOP clause can retrieve the highest or lowest values according to the specified order.

---

Practice questions 01:

Q29. Create a query to display employee names and their annual salaries using aliases.

SELECT ENAME, SAL 12 AS ANNUAL_SALARY FROM EMP;

Q30. Write a SQL query to show employee names along with their total salary (base salary plus 20% bonus) and use aliases to label the calculated columns.

SELECT ENAME, SAL AS BASE_SALARY, SAL 0.2 AS BONUS, SAL + (SAL 0.2) AS TOTAL_SALARY FROM EMP;

Q31. Generate a query to display employee names, salaries, and their department with aliases for the calculated columns.

SELECT ENAME AS EMPLOYEE_NAME, SAL AS SALARY, DEPTNO AS DEPARTMENT_NUMBER FROM EMP;

Q32. Write a query to display employee names and their experience (calculated as the difference between the current date and the hire date), using an alias for the calculated column.

SELECT ENAME, ROUND(DATEDIFF(YEAR, HIREDATE, GETDATE())) AS EXPERIENCE_YEARS FROM EMP;

Q33. Display all employee details sorted by their salaries in descending order.

SELECT FROM EMP ORDER BY SAL DESC;

Q34. Write a SQL query to sort employee records first by department in ascending order and then by hire date in descending order within each department.

SELECT FROM EMP ORDER BY DEPTNO ASC, HIREDATE DESC;

Q35. Retrieve employee details ordered by job title in ascending order and then by salary in descending order.

SELECT FROM EMP ORDER BY JOB ASC, SAL DESC;

Q36. Show a list of employees ordered by their names in alphabetical order, and then by hire date in descending order if names are the same.

SELECT FROM EMP ORDER BY ENAME ASC, HIREDATE DESC;

Q37. Write a query to list all unique job titles from the employee table.

SELECT DISTINCT JOB FROM EMP;

Q38. Create a query to find distinct department numbers from the employee table.

SELECT DISTINCT DEPTNO FROM EMP;

Q39. Generate a query to show unique combinations of job title and department number.

SELECT DISTINCT JOB, DEPTNO FROM EMP;

Q40. Write a query to find unique salary figures from the employee table.

SELECT DISTINCT SAL FROM EMP;

Q41. Retrieve the top 5 highest-paid employees from the employee table.

SELECT TOP 5 FROM EMP ORDER BY SAL DESC;

Q42. Show the top 10 employees based on their years of experience (sorted from most experienced to least experienced).

SELECT TOP 10 FROM EMP ORDER BY DATEDIFF(YEAR, HIREDATE, GETDATE()) DESC;

Q43. Write a query to get the top 3 employees with the lowest salaries.

SELECT TOP 3 FROM EMP ORDER BY SAL ASC;

Q44. Display the top 4 employees who joined the company most recently.

SELECT TOP 4 FROM EMP ORDER BY HIREDATE DESC;

Q45. What is the purpose of using an alias in SQL? Can you provide an example where it improves the readability of a query?

Purpose of Alias: An alias in SQL is used to provide a temporary name to a table or column for the duration of a query. This can make queries more readable and easier to understand, especially when dealing with complex expressions or multiple tables.

Q46. How does using an alias for a column differ from using an alias for a table? Provide an example of each.

When you need to simplify complex expressions: SELECT SAL 0.2 AS HRA FROM EMP; .

Q47. How does the ORDER BY clause affect the result set of a query? Can you explain the difference between ascending and descending order?

ORDER BY Clause: It sorts the result set based on one or more columns in either ascending or descending order. By default, sorting is done in ascending order.

Q48. If you need to sort a result set by multiple columns, how do you specify the sorting order for each column? Can you provide an example query?

Multiple Columns: You can specify the sorting order for each column in the ORDER BY clause, separating them with commas. SELECT FROM EMP ORDER BY DEPTNO ASC, SAL DESC;

Q49. Explain how the ORDER BY clause can be used in conjunction with other SQL clauses like LIMIT or OFFSET for pagination.

Pagination: Use ORDER BY to sort the results and LIMIT or OFFSET to fetch a specific subset of the result set.

Q50. What does the DISTINCT clause do in SQL? Can you provide an example where it is used to eliminate duplicate rows?

DISTINCT Clause: Removes duplicate rows from the result set. SELECT DISTINCT DEPTNO FROM EMP; .

Q51. This retrieves unique department numbers from the EMP table.

Multiple Columns:DISTINCT applies to the combination of all specified columns, removing duplicates based on those combinations. SELECT DISTINCT DEPTNO, JOB FROM EMP; .

Q52. Can you explain a scenario where DISTINCT might be used incorrectly, and what the consequences would be?

Incorrect Use: Using DISTINCT when it’s unnecessary can lead to inefficient queries.

Q53. How would you use the TOP clause to retrieve the top 10 highest salaries from an employee table? Provide a sample query.

TOP Clause for Highest Salaries: SELECT TOP 10 FROM EMP ORDER BY SAL DESC;

Q54. Explain how to combine the TOP clause with the ORDER BY clause to get the top N rows based on a specific sorting criterion.

Combining TOP with ORDER BY:

#SQL Sub-languages:

DML Commands (Data manuipulation Lang):

1. INSERT

2. UPDATE

3. DELETE

4. MARGE

Characteristics:

1. All DML commands act on table data.

All DML commands act on table data: This means that Data Manipulation Language (DML) commands, such as INSERT, UPDATE, DELETE, and MERGE, are used to modify the data stored in database tables.

2. All DML operations are auto-committed by default.

All DML operations are auto-committed by default: By default, when you execute a DML command, the changes are automatically saved to the database. This automatic saving of changes is known as auto-commit.

3. To stop auto-commit, execute: SET IMPLICIT_TRANSACTIONS ON.

To stop auto-commit, execute: SET IMPLICIT_TRANSACTIONS ON: If you want to manually control when changes are saved to the database, you can turn off auto-commit by executing the command SET IMPLICIT_TRANSACTIONS ON. This command ensures that changes are not automatically committed until you explicitly save them.

4. To save the operation, execute COMMIT.

To save the operation, execute COMMIT: When IMPLICIT_TRANSACTIONS is on, you need to use the COMMIT command to save your changes permanently to the database. This is a way to confirm that you want the changes to be applied.

5. To cancel the operation, execute ROLLBACK.

To cancel the operation, execute ROLLBACK: If you decide you do not want to keep the changes made during the transaction, you can use the ROLLBACK command. This command undoes all the changes made since the last commit, effectively cancelling the transaction.

UPDATE Command:

The UPDATE command is used to modify table data. It can update all rows or specific rows, and can target single or multiple columns.

Syntax:

UPDATE table_name
SET column_name = value, column_name = value, ...
[WHERE condition]

Example:

1. Update all employees' commission to 500:

    UPDATE EMP
    SET COMM = 500
   

2. Update employees' commission to 500 where commission is NULL:

    UPDATE EMP
    SET COMM = 500
    WHERE COMM IS NULL
   

3. Update employees' commission to NULL where commission is not NULL:

    UPDATE EMP
    SET COMM = NULL
    WHERE COMM IS NOT NULL
   

4. Update salary to 2000 and commission to 800 where employee number is 7369:

    UPDATE EMP
    SET SAL = 2000, COMM = 800
    WHERE EMPNO = 7369
   

5. Increase salary by 20% and commission by 10% for salesmen hired in 1981:

    UPDATE EMP
    SET SAL = SAL * 1.2, COMM = COMM * 1.1
    WHERE JOB = 'SALESMAN'
    AND HIREDATE LIKE '1981%'
   

6. Transfer all employees from department 10 to department 20:

    UPDATE EMP
    SET DEPTNO = 20
    WHERE DEPTNO = 10
   

7. Increase prices by 10% for Samsung, Redmi, and Realme mobile phones:

    UPDATE PRODUCTS
    SET PRICE = PRICE * 1.1
    WHERE CATEGORY = 'mobiles'
    AND BRAND IN ('samsung', 'redmi', 'realme')
   

DELETE command:

The DELETE command is used to remove row(s) from a table in a database.

Syntax:

DELETE FROM <table_name> [WHERE condition];

Examples:

1. Delete all rows from theEMP table:

    DELETE FROM EMP;
   

   This command removes all records from the EMP table. Use this with caution, as it will delete all data without a condition.

2. Delete employees who joined in the 2nd quarter of the year 1981:

    DELETE FROM EMP
    WHERE HIREDATE BETWEEN '1981-04-01' AND '1981-06-30';
   

   This command deletes rows from the EMP table where the HIREDATE falls within the second quarter of 1981, specifically between April 1st and June 30th, 1981.

Make sure to always use the WHERE clause with the DELETE command to avoid removing unintended rows. If no WHERE clause is provided, all rows in the table will be deleted.

DDL Commands (Data Definition Lang):

DDL commands are used to define and modify the structure of database objects such as tables.

Common DDL Commands:

• CREATE: Used to create a new table or database object.

• ALTER: Used to modify an existing table's structure.

• DROP: Used to remove a table or other database objects.

• TRUNCATE: Used to remove all rows from a table without deleting the table structure.

ALTER Command:

The ALTER command modifies the structure of an existing table.

Uses ofALTER Command:

1. Adding Columns:

   • To add a new column to a table.

   • Example: Add a column GENDER to the EMP table:

       ALTER TABLE EMP ADD GENDER CHAR(1);
     

     After adding, the new column is filled with NULL by default. To update the new column with data, use the UPDATE command:

       UPDATE EMP SET GENDER = 'M'
       WHERE EMPNO = 7369;
     

2. Dropping Columns:

   • To remove an existing column from a table.

   • Example: Drop the column GENDER from the EMP table:

       ALTER TABLE EMP
       DROP COLUMN GENDER;
     

3. Modifying Columns:

   • To change the data type or size of an existing column.

   • Example: Change the data type of the EMPNO column to INT:

       ALTER TABLE EMP
       ALTER COLUMN EMPNO INT;
     

   • Example: Increase the size of the ENAME column to 20 characters:

       ALTER TABLE EMP
       ALTER COLUMN ENAME VARCHAR(20);
     

DROP Command:

The DROP command is used to remove a table from the database. This command deletes the table structure along with all the data contained in it.

• Syntax:

    DROP TABLE <table_name>;
  

• Example:

    DROP TABLE EMP;
  

TRUNCATE Command:

The TRUNCATE command deletes all data from a table but keeps the table structure. It also frees up the memory used by the data.Syntax:

•                 TRUNCATE TABLE <table_name>;
  

• Example:

    TRUNCATE TABLE EMP;
  

Use TRUNCATE and DROP commands with caution as they affect all rows in the table and can lead to data loss.

DELETE vs. TRUNCATE

SP_RENAME

SP_RENAME is used to change the name of a table or a column.

• Syntax:

    SP_RENAME 'old_name', 'new_name';
  

• Examples:

  • Rename table EMP to EMPLOYEES:

      SP_RENAME 'EMP', 'EMPLOYEES';
    

  • Rename column COMM to BONUS in the EMPLOYEES table:

      SP_RENAME 'EMPLOYEES.COMM', 'BONUS';
    

List of Tables Created-by-user:

To list all tables created by the user in the database, use:

SELECT * FROM INFORMATION_SCHEMA.TABLES;

The command SELECT * FROM INFORMATION_SCHEMA.TABLES is a SQL query used to retrieve information about the tables within a database.

INFORMATION_SCHEMA is a standardized set of read-only views that provide metadata about the database objects, such as tables, columns, data types, and constraints. These views are part of the SQL standard and are supported by many database systems, including SQL Server, MySQL, and PostgreSQL.

This comparison and usage information helps clarify when to use DELETE or TRUNCATE and how to rename database objects using SP_RENAME.

---

Built-in Functions in SQL Server:

In SQL Server, built-in functions are predefined functions that perform calculations and return a single value based on the input provided. These functions help in data manipulation, querying, and analysis. A function accepts some input performs some calculation and returns one value.

Types of Built-in Functions:

1. Date Functions:

   • Functions that deal with date and time data types. Examples include GETDATE(), DATEPART(), DATENAME(), DATEADD(), DATEDIFF(), EOMONTH(), and FORMAT().

2. String Functions:

   • Functions used for manipulating string data. Examples include LEN(), LOWER(), LEFT(), SUBSTRING(), CHARINDEX(), RIGHT(), REPLACE(), REPLICATE(), TRANSLATE(), STUFF(), and UPPER().

3. Numeric Functions:

   • Functions that perform operations on numeric data. Examples include ABS(), CEILING(), FLOOR(), ROUND(), and POWER().

4. Conversion Functions:

   • Functions that convert data from one data type to another. Examples include CAST(), CONVERT(), and PARSE().

5. Special Functions:

   • Functions that perform specialized tasks, such as COALESCE(), NULLIF(), and ISNULL().

6. Analytical Functions:

   • Functions used for advanced data analysis, often involving windowing and ranking. Examples include ROW_NUMBER(), RANK(), DENSE_RANK(), and LEAD().

7. Aggregate Functions:

   • Functions that perform calculations on a set of values and return a single value. Examples include SUM(), AVG(), COUNT(), MIN(), and MAX().

DATE Functions in SQL Server:

GETDATE():

• This function returns the current date and time.

• Example: SELECT GETDATE() might return 2023-09-21 19:15:59.130.

DATEPART():

• Used to extract a specific part of a date, such as year, month, day, etc.

• Syntax: DATEPART(interval, date)

• Example:

  • SELECT DATEPART(yy, GETDATE()) => 2023 (Year)

  • SELECT DATEPART(mm, GETDATE()) => 09 (Month)

  • SELECT DATEPART(dd, GETDATE()) => 21 (Day)

  • SELECT DATEPART(dy, GETDATE()) => 264 (Day of the year)

  • SELECT DATEPART(dw, GETDATE()) => 5 (Day of the week)

  • SELECT DATEPART(hh, GETDATE()) => Hour

  • SELECT DATEPART(mi, GETDATE()) => Minutes

  • SELECT DATEPART(ss, GETDATE()) => Seconds

  • SELECT DATEPART(qq, GETDATE()) => 3 (Quarter)

Q. Display ENAME , SAL , year of join ?

SELECT ENAME,SAL,DATEPART(YY,HIREDATE) AS YEAR FROM EMP

Q. Display employees joined in 1980,1983,1985 ?

SELECT FROM EMP WHERE DATEPART(YY,HIREDATE) IN (1980,1983,1985)

Q. Employees joined in leap year ?

SELECT FROM EMP WHERE DATEPART(yy,hiredate)%4=0

Q. Employees joined in jan,apr,dec months ?

SELECT FROM EMP WHERE DATEPART(MM,HIREDATE) IN (1,4,12)

Q. Employees joined in 2nd quarter of 1981 year ?

SELECT FROM EMP WHERE DATEPART(QQ,HIREDATE)=2 AND DATEPART(YY,HIREDATE)=1981

DATENAME():

• Used to get the name of the specified date part.

• Example:

  • SELECT DATENAME(dw, GETDATE()) => Friday

  • To find employees who joined on Sunday: SELECT * FROM EMP WHERE DATENAME(dw, HIREDATE) = 'SUNDAY'.

Q. Display ENAME DAY OF THE WEEK ?

SELECT ENAME,DATENAME(DW,HIREDATE) AS DAY FROM EMP

Q. Write a query to display on which day india got independence ?

SELECT DATENAME(DW,'1947-08-15')

FORMAT():

• Formats a date/time value into a specified format.

• Syntax: FORMAT(date, 'format')

• Examples:

  • SELECT FORMAT(GETDATE(), 'dd.MM.yyyy') => 22.09.2023

  • SELECT FORMAT(GETDATE(), 'HH:mm:ss') => 18:51:20

  • SELECT FORMAT(GETDATE(),'hh:mm:ss') => 6:51:23

  • SELECT FORMAT(GETDATE(), 'MM/dd/yyyy') for date formatting in MM/DD/YYYY.

Q. Display ENAME HIREDATE ? display hiredate in MM/DD/YYYY format ?

SELECT ENAME,FORMAT(HIREDATE,'MM/dd/yyyy') AS HIREDATE FROM EMP

Scenario:

INSERT INTO EMP(EMPNO,ENAME,SAL,HIREDATE) VALUES(999,'ABC',5000,GETDATE())

List of employees joined today ?

SELECT * FROM EMP WHERE HIREDATE = GETDATE() => NO ROWS

2023-09-22 = 2023-09-22 18:58:33.133

SELECT * FROM EMP WHERE HIREDATE = FORMAT(GETDATE(),'yyyy-MM-dd')

2023-09-22 = 2023-09-22

DATEADD():

• Adds or subtracts a specified number of units to/from a date(used to add / subtract days,months,years to / from a date).

• Syntax: DATEADD(interval, number, date)

• Examples:

  • SELECT DATEADD(yy, 1, GETDATE()) => Adds 1 year.

  • SELECT DATEADD(mm, 2, GETDATE()) => Adds 2 months.

  • SELECT DATEADD(dd, -10, GETDATE()) => Subtracts 10 days.

Q. Display today's gold rate ?

SELECT FROM GOLD_RATES WHERE DATEID = FORMAT(GETDATE(),'yyyy-MM-dd')

Q. Display yesterday's gold rate ?

SELECT FROM GOLD_RATES WHERE DATEID = FORMAT(DATEADD(DD,-1,GETDATE()),'yyyy-MM-dd')

Q. Display last month same day gold rate ?

SELECT FROM GOLD_RATES WHERE DATEID = FORMAT(DATEADD(MM,-1,GETDATE()),'yyyy-MM-dd')

Q. Display last year same day gold rate ?

SELECT FROM GOLD_RATES WHERE DATEID = FORMAT(DATEADD(YY,-1,GETDATE()),'yyyy-MM-dd')

Q. Last 1 month gold rates ?

SELECT * FROM GOLD_RATES WHERE DATEID BETWEEN FORMAT(DATEADD(MM,-1,GETDATE()),'yyyy-MM-dd') AND FORMAT(GETDATE(),'yyyy-MM-dd')

DATEDIFF():

• Calculates the difference between two dates in the specified interval.

• Syntax: DATEDIFF(interval, startdate, enddate)

• Examples:

  • SELECT DATEDIFF(yy, '2022-09-22', GETDATE()) => Difference in years.

  • SELECT DATEDIFF(mm, '2022-09-22', GETDATE()) => Difference in months.

Q. Display ENAME EXPERIENCE in years ?

SELECT ENAME,DATEDIFF(YY,HIREDATE,GETDATE()) AS EXPERIENCE FROM EMP

Q. Display ENAME EXPERIENCE ? M YEARS N MONTHS

SELECT ENAME, DATEDIFF(MM, HIREDATE, GETDATE()) / 12 AS YEARS, DATEDIFF(MM, HIREDATE, GETDATE()) % 12 AS MONTHS FROM EMP

EOMONTH():

• Returns the last day of the month for a given date, with an optional offset.

• Syntax: EOMONTH(date, offset)

• Examples:

  • SELECT EOMONTH(GETDATE(), 0) => Last day of the current month.

  • SELECT EOMONTH(GETDATE(), 1) => Last day of the next month.

  • SELECT EOMONTH(GETDATE(), -1) => Last day of the previous month.

Examples:

• To display an employee's name and their experience in years and months:

    SELECT ENAME,
           DATEDIFF(mm, HIREDATE, GETDATE()) / 12 AS YEARS,
           DATEDIFF(mm, HIREDATE, GETDATE()) % 12 AS MONTHS
    FROM EMP;
  

• To find all employees who joined in the second quarter of 1981:

    SELECT * FROM EMP
    WHERE DATEPART(qq, HIREDATE) = 2
      AND DATEPART(yy, HIREDATE) = 1981;
  

Q. Display next month first day ?

Q. Display current month first day ?

Q. Display next year first day ?

Q. Display current year first day ?

String Functions in SQL Server:

UPPER():

• Converts a string to uppercase.

    UPPER(arg)
  

  Example:

    SELECT UPPER('hello') => HELLO
  

LOWER():

• Converts a string to lowercase.

    LOWER(arg)
  

  Example:

    SELECT LOWER('HELLO') => hello
  

  • Display ENAME and SAL in lowercase:

      SELECT LOWER(ENAME) AS ENAME, SAL FROM EMP
    

  • Convert names to lowercase in the table:

      UPDATE EMP SET ENAME = LOWER(ENAME)
    

LEN():

• Returns the length of a string (i.e., number of characters).

    LEN(arg)
  

  Example:

    SELECT ENAME, LEN(ENAME) AS LEN FROM EMP
  

  • Display employees' names with 4 characters:

      SELECT *
      FROM EMP
      WHERE LEN(ENAME) = 4
    

LEFT():

• Returns the specified number of characters from the left side of a string.

    LEFT(STRING, NO_OF_CHARS)
  

  Example:

    SELECT LEFT('HELLO WELCOME', 5) => HELLO
  

RIGHT():

• Returns the specified number of characters from the right side of a string.

    RIGHT(STRING, NO_OF_CHARS)
  

  Example:

    SELECT RIGHT('HELLO WELCOME', 7) => WELCOME
  

• Employees whose names start with 's':

    SELECT * FROM EMP WHERE LEFT(ENAME, 1) = 's'
  

• Employees whose names end with 's':

    SELECT * FROM EMP WHERE RIGHT(ENAME, 1) = 's'
  

• Employees whose names start and end with the same character:

    SELECT * FROM EMP WHERE LEFT(ENAME, 1) = RIGHT(ENAME, 1)
  

Scenario:

• Generate email IDs for employees:

    SELECT empno, ename,
    LEFT(ename, 3) + LEFT(empno, 3) + '@tcs.com' AS emailid
    FROM emp
  

• Store email IDs in the database:

  • Step 1: Add emailid column to the emp table:

      ALTER TABLE EMP
      ADD EMAILID VARCHAR(30)
    

  • Step 2: Update the column with email IDs:

      UPDATE EMP
      SET EMAILID = LEFT(ename, 3) + LEFT(empno, 3) + '@tcs.com'
    

SUBSTRING():

• Extracts a part of a string starting from a specific position.

    SUBSTRING(string, start, length)
  

  Example:

    SELECT SUBSTRING('HELLO WELCOME', 7, 4) => WELC
    SELECT SUBSTRING('HELLO WELCOME', 10, 3) => COM
  

REPLICATE():

• Repeats a given character a specified number of times.

    REPLICATE(char, length)
  

  Example:

    SELECT REPLICATE('*', 5) => *****
  

• Display ENAME and SAL with masked salary:

    SELECT ENAME, REPLICATE('*', LEN(SAL)) AS SAL FROM EMP
  

Scenario:

• Masking account numbers:

    SELECT REPLICATE('X', 4) + RIGHT(ACCNO, 4) AS MASKED_ACCNO
    FROM ACCOUNTS
  

REPLACE():

• Replaces occurrences of a specified string with another string.

    REPLACE(str1, str2, str3)
  

  Example:

    SELECT REPLACE('hello', 'ell', 'abc') => habco
    SELECT REPLACE('hello', 'l', 'abc') => heabcabco
    SELECT REPLACE('@@he@@ll@@o@@', '@', '') => hello
  

• Display employees whose names contain exactly one 'a':

    SELECT *
    FROM EMP
    WHERE LEN(ENAME) - LEN(REPLACE(ENAME, 'A', '')) = 1
  

TRANSLATE():

• Translates one character to another in a string.

    TRANSLATE(str1, str2, str3)
  

  Example:

    SELECT TRANSLATE('hello', 'elo', 'abc') => habbc
  

• Encryption example:

    SELECT ENAME, TRANSLATE(SAL, '0123456789.', '$bT*h@U%#^&') AS SAL
    FROM EMP
  

• Remove special characters from a string:

    SELECT REPLACE(TRANSLATE('%@he*&ll^$o@#', '%@*&^$#', '*******'), '*', '')
  

NOTE :- Translate function can be used to encrypt data i.e. converting plain text to cipher text.

STUFF():

• Replaces a part of a string with another string, based on a starting position and length.

    STUFF(str1, start, length, str2)
  

  Example:

    SELECT STUFF('hello welcome', 10, 3, 'abc') => hello welabce
  

CHARINDEX():

• Returns the starting position of a specified substring within a string.

    CHARINDEX(char, string, [start])
  

  Example:

    SELECT CHARINDEX('o', 'hello welcome') => 5
    SELECT CHARINDEX('x', 'hello welcome') => 0
    SELECT CHARINDEX('o', 'hello welcome', 6) => 11
    SELECT CHARINDEX('e', 'hello welcome', 10) => 13
  

Scenario:

• Split full names into first and last names:

    SELECT CID,
           SUBSTRING(CNAME, 1, CHARINDEX(' ', CNAME) - 1) AS FNAME,
           SUBSTRING(CNAME, CHARINDEX(' ', CNAME) + 1, LEN(CNAME)) AS LNAME
    FROM CUST
  

Q. Split names into first, middle, and last names.

Numeric Functions in SQL Server:

Let's include the descriptions for the POWER() and ABS() functions:

ROUND():

• Rounds a number to a specified number of decimal places or to an integer(rounds number to integer or to decimal places based on avg).

    ROUND(number, decimal_places)
  

  Examples:

  • Rounding to the nearest integer:

      ROUND(38.5678, 0) => 39
      ROUND(38.3647, 0) => 38
    

  • Rounding to two decimal places:

      ROUND(38.5638, 2) => 38.56
      ROUND(38.5678, 2) => 38.57
    

  • Rounding to one decimal place:

      ROUND(38.5678, 1) => 38.6
    

  • Rounding to the nearest hundred:

      ROUND(386, -2) => 400
    

  • Rounding to the nearest ten:

      ROUND(386, -1) => 390
    

  • Rounding to the nearest thousand:

      ROUND(386, -3) => 0
    

  • Round all employee salaries to the nearest hundred:

      UPDATE EMP SET SAL = ROUND(SAL, -2)
    

CEILING():

• Rounds a number up to the nearest integer.

    CEILING(number)
  

  Example:

    SELECT CEILING(3.1) => 4
  

FLOOR():

• Rounds a number down to the nearest integer.

    FLOOR(number)
  

  Example:

    SELECT FLOOR(3.9) => 3
  

POWER():

• Raises a number to the power of another number.

    POWER(base, exponent)
  

  Example:

    SELECT POWER(2, 3) => 8  -- 2 raised to the power of 3
  

ABS():

• Returns the absolute value of a number, removing any negative sign.

    ABS(number)
  

  Example:

    SELECT ABS(-5) => 5
    SELECT ABS(5) => 5
  

Conversion Functions in SQL Server:

1. CAST

2. CONVERT

3. PARSE

All these functions are used to convert data from one type to another.

CAST():

• Converts an expression of one data type to another data type.

    CAST(source_value AS target_type)
  

  Examples:

  • Converting a decimal to an integer:

      SELECT CAST(10.5 AS INT) => 10
    

  • Displaying employee names and salaries with a string concatenation:

      SELECT ENAME + ' earns ' + CAST(SAL AS VARCHAR) 
      FROM EMP
    

  • Displaying a message with hire date and job:

      SELECT ENAME + ' joined on ' + CAST(HIREDATE AS VARCHAR) + ' as ' + JOB
      FROM EMP
    

Q. Display smith joined on 1980-12-17 as clerk ?

SELECT ename + ' joined on ' + CAST(hiredate AS VARCHAR) + ' as ' + job FROM emp

CONVERT():

• Similar to CAST, but with more flexibility for date and monetary formats.

    CONVERT(target_type, source_value, [style])
  

  Examples:

  • Converting a decimal to an integer:

      SELECT CONVERT(INT, 10.5) => 10
    

Differences between CAST and CONVERT:

1. Using convert we can display dates in different formats but not possible using cast.

2. Using convert we can display money in different formats but not possible using cast.

Date Styles inCONVERT:

• Use CONVERT to display dates in different formats with style numbers.

    CONVERT(VARCHAR, date, style_number)
  

  Examples:

  • U.S. style (MM/DD/YYYY):

      SELECT CONVERT(VARCHAR, GETDATE(), 101) => 09/26/2023
    

  • British/French style (DD MMM YYYY):

      SELECT CONVERT(VARCHAR, GETDATE(), 106) => 26 Sep 2023
    

  • Time in HH:MM:SS:MMM format:

      SELECT CONVERT(VARCHAR, GETDATE(), 114) => 19:06:44:713
    

  • Displaying hire dates in a specific format:

      SELECT ENAME, CONVERT(VARCHAR, HIREDATE, 105) AS HIREDATE
      FROM EMP
    

Money Styles inCONVERT:

• Formats monetary values with different style numbers.

    CONVERT(VARCHAR, money, style_number)
  

  Examples:

  • No style specified (default):

      SELECT CONVERT(VARCHAR, SAL, 0) FROM EMP  -- 2 digits after the decimal
    

  • With a thousand separator:

      SELECT CONVERT(VARCHAR, SAL, 1) FROM EMP
    

  • Displaying salaries with a thousand separator:

      SELECT ENAME, CONVERT(VARCHAR, SAL, 1) AS SAL
      FROM EMP
    

PARSE():

• Converts a string to a specified data type, often used for parsing date and numeric values from strings.

    PARSE(source_value AS target_type [USING culture])
  

  Examples:

  • Parsing a string to a datetime:

      SELECT PARSE('2023-09-26' AS DATETIME) => 2023-09-26 00:00:00.000
    

  • Parsing a string to a numeric type:

      SELECT PARSE('12345.67' AS DECIMAL(10, 2)) => 12345.67
    

  • Using a specific culture for parsing:

      SELECT PARSE('26 Sep 2023' AS DATETIME USING 'en-US') => 2023-09-26 00:00:00.000
    

  • Parsing and formatting data:

      SELECT ENAME, PARSE(SAL AS DECIMAL(10, 2)) AS SAL FROM EMP
    

The PARSE() function is especially useful when dealing with data that includes regional formatting, such as different date formats or numeric separators.

Special Functions in SQL Server:

ISNULL():

• The ISNULL() function in SQL is used to replace NULL values with a specified replacement value (used to convert null values).

• It takes two arguments: the expression to check for NULL and the value to return if the expression is NULL.

ISNULL(expression, replacement_value)

• Behavior:

  • If expression is not NULL, then ISNULL() returns the value of expression (or you can say:-if( expression<> null) returns expression).

  • If expression is NULL, ISNULL() returns replacement_value (or you can say:- if(expression = null) then returns replacement_value).

Examples:

1. Basic Usage:

    SELECT ISNULL(100, 200) => 100
    SELECT ISNULL(NULL, 200) => 200
   

2. Displaying Employee Information:

   • Displaying Total Salary (TOTSAL):

     • The query below calculates the total salary (TOTSAL) by adding SAL and COMM, using ISNULL() to handle NULL values in COMM.

        SELECT ENAME, SAL, COMM, SAL + ISNULL(COMM, 0) AS TOTSAL
        FROM EMP

Output:

        ENAME     | SAL    | COMM   | TOTSAL
        --------------------------------------
        WARD      | 1300.00| 500.00 | 1800.00
        JONES     | 3000.00| NULL   | 3000.00

• Handling NULL COMM Values:

  • If COMM is NULL, display 'N/A' instead.

  • The CAST() function is used to convert COMM to a VARCHAR type to allow the display of 'N/A'.

        SELECT ENAME, SAL, ISNULL(CAST(COMM AS VARCHAR), 'N/A') AS COMM
        FROM EMP

Output:

        ENAME     | SAL    | COMM
        -------------------------
        WARD      | 1300.00| 500.00
        JONES     | 3000.00| N/A

Note:

• The ISNULL() function is particularly useful when dealing with optional columns or fields that may contain NULL values, ensuring that reports and calculations do not encounter errors due to NULL data.

COALESCE():

• The COALESCE() function in SQL returns the first non-NULL value from a list of expressions. If all expressions are NULL, it returns NULL.

COALESCE(expression1, expression2, ..., expressionN)

• Behavior:

  • Evaluates the expressions in order and returns the first non-NULL expression.

  • If all expressions are NULL, it returns NULL.

Examples:

1. Basic Usage:

    SELECT COALESCE(NULL, NULL, 'default') => 'default'
    SELECT COALESCE(NULL, 'value1', 'value2') => 'value1'
   

2. Display Employee Salary or Commission:

   • This example returns the salary if it exists; otherwise, it returns the commission, and if both are NULL, it returns 'N/A'.

    SELECT ENAME, COALESCE(SAL, COMM, 'N/A') AS INCOME
    FROM EMP

NULLIF():

• The NULLIF() function returns NULL if the two specified expressions are equal; otherwise, it returns the first expression.

NULLIF(expression1, expression2)

• Behavior:

  • Compares two expressions: if they are equal, NULLIF() returns NULL.

  • If they are not equal, it returns the first expression.

Examples:

1. Basic Usage:

    SELECT NULLIF(100, 100) => NULL
    SELECT NULLIF(100, 200) => 100
   

2. Avoid Division by Zero:

   • NULLIF() can be useful in calculations where dividing by zero may occur.

   • The example below ensures that division by zero does not happen by returning NULL instead.

    SELECT ENAME, SAL / NULLIF(COMM, 0) AS SALARY_TO_COMM_RATIO
    FROM EMP

• Here, if COMM is 0, NULLIF(COMM, 0) returns NULL, preventing a division by zero error.

These functions (ISNULL(), COALESCE(), and NULLIF()) are essential in handling NULL values in SQL, providing flexibility in data manipulation and ensuring data integrity.

Analytical Functions in SQL Server:

RANK & DENSE_RANK:

• Both functions are used to find ranks.

• Ranking is based on a specific column.

• For rank functions, the data must be sorted.

RANK():

• The RANK() function assigns a rank to each row within a result set like (find the ranks of the employees based on sal and highest paid employee should get 1st rank ?).

• Ranks may have gaps when there are ties (i.e., rows with the same values).

RANK() OVER (ORDER BY colname ASC/DESC)

DENSE_RANK():

• The DENSE_RANK() function also assigns ranks to rows but without gaps, even if there are ties.

DENSE_RANK() OVER (ORDER BY colname ASC/DESC)

Examples:

1. Finding Ranks Based on Salary:

   • Highest paid employee should get the 1st rank.

    SELECT empno, ename, sal,
           RANK() OVER (ORDER BY sal DESC) AS rnk
    FROM emp;
    --Or
    SELECT empno, ename, sal,
           DENSE_RANK() OVER (ORDER BY sal DESC) AS drnk
    FROM emp;

2. Difference Between RANK and DENSE_RANK:

   • RANK can produce gaps in the ranking sequence.

   • DENSE_RANK does not produce gaps.

   • In rank function ranks are not in sequence but in dense_rank ranks are always in sequence

3. Finding Ranks Based on Salary and Hire Date:

   • Ranking by salary first, and if salaries are the same, ranking by hire date.

    SELECT empno, ename, hiredate, sal,
           DENSE_RANK() OVER (ORDER BY sal DESC, hiredate ASC) AS drnk
    FROM emp;

PARTITION BY Clause:

• Used with ranking functions to find ranks within a group.

• For example, finding ranks within departments.

Example:

SELECT empno, ename, sal, deptno,
       DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) AS rnk
FROM emp;

ROW_NUMBER():

• Assigns a unique sequential integer to rows within a partition of a result set.

• Returns record numbers.

• It is also based on some column.

• Data must be sorted

• Useful for pagination and uniquely ordering data.

ROW_NUMBER() OVER (ORDER BY colname ASC/DESC)

Example:

SELECT empno, ename, sal,
       ROW_NUMBER() OVER (ORDER BY empno ASC) AS rno
FROM emp;

• SAL: The salary of the employees.

• RNK: The rank assigned using the RANK() function, which may have gaps.

• DRNK: The rank assigned using the DENSE_RANK() function, without gaps.

• RNO: The row number assigned using the ROW_NUMBER() function.

LAG & LEAD:

• LAG() provides access to a row at a given physical offset before the current row within the result set (LAG(colname,int) OVER (ORDER BY---) => returns previous value).

• LEAD() provides access to a row at a given physical offset after the current row (LEAD(colname,int) OVER (ORDER BY---) => returns next value).

LAG():

LAG(colname, offset) OVER (ORDER BY colname ASC/DESC)

LEAD():

LEAD(colname, offset) OVER (ORDER BY colname ASC/DESC)

Example:

1. Using LAG to Get Previous Salary:

    SELECT empno, ename, sal,
           LAG(sal, 1) OVER (ORDER BY empno ASC) AS prev_sal
    FROM emp;
   

2. Calculating the Number of Days Between Hires:

    SELECT ename, hiredate,
           DATEDIFF(DD, LAG(hiredate, 1) OVER (ORDER BY hiredate ASC), hiredate) AS days
    FROM emp;
   

   | EMPNO | ENAME | SAL | PREV_SAL | | --- | --- | --- | --- | | 7369 | Smith | 2000.00 | NULL | | 7499 | Allen | 1600.00 | 2000.00 | | 7521 | Ward | 1250.00 | 1600.00 |

Q. Display ENAME HIREDATE DAYS ?

SELECT ENAME,HIREDATE, DATEDIFF(DD, LAG(HIREDATE,1) OVER (ORDER BY HIREDATE ASC), HIREDATE) AS DAYS FROM EMP

Out:-

Aggregate Functions/ Group Functions in SQL Server:

Aggregate functions process a group of rows and return a single value. They include functions such as MAX, MIN, SUM, AVG, COUNT and COUNT(*).

MAX()

• Description: Returns the maximum value from a set of values.

• Syntax: MAX(expression)

• Examples:

  • SELECT MAX(SAL) FROM EMP; → Returns 5000

  • SELECT MAX(HIREDATE) FROM EMP; → Returns '1983-01-12'

  • SELECT MAX(ENAME) FROM EMP; → Returns 'WARD'

MIN()

• Description: Returns the minimum value from a set of values.

• Syntax: MIN(expression)

• Examples:

  • SELECT MIN(SAL) FROM EMP; → Returns 800

  • SELECT MIN(HIREDATE) FROM EMP; → Returns '1980-12-17'

SUM()

• Description: Returns the sum of a numeric column.

• Syntax: SUM(expression)

• Examples:

  • SELECT SUM(SAL) FROM EMP; → Returns 29300.00

  • To round the total salary to the nearest thousand:

    • SELECT ROUND(SUM(SAL), -3) FROM EMP; → Returns 29000

  • To display the total salary with a thousand separator:

    • SELECT CONVERT(VARCHAR, ROUND(SUM(SAL), -3), 1) FROM EMP; → Returns '29,000.00'

  • To calculate total salary including commissions:

    • SELECT SUM(SAL + COMM) FROM EMP;

      or

      SELECT SUM(SAL + ISNULL(COMM, 0)) FROM EMP;

AVG()

• Description: Returns the average value of a numeric column.

• Syntax: AVG(expression)

• Examples:

  • SELECT AVG(SAL) FROM EMP; → Returns 2092.8571

  • To round the average salary down to the nearest integer:

    • SELECT FLOOR(AVG(SAL)) FROM EMP; → Returns 2092

Note: TheSUM and AVG functions cannot be applied to date or character columns.

COUNT()

• Description: Counts the number of non-null values in a column.

• Syntax: COUNT(expression)

• Examples:

  • SELECT COUNT(EMPNO) FROM EMP; → Returns 14

  • SELECT COUNT(COMM) FROM EMP; → Returns 4 (Null values are not counted)

COUNT(*)

• Description: Counts the total number of rows in a table, including those with null values.

• Syntax: COUNT(*)

• Examples:

  • SELECT COUNT(*) FROM EMP; → Returns 14

Differences Between COUNT and COUNT(*)

• COUNT(expression) does not include nulls.

• COUNT(*) includes nulls and not-null.

Example: Given a table T1 with a column F1 containing values (10, NULL, 20, NULL, 30):

• COUNT(F1) → Returns 3 (Only non-null values are counted)

• COUNT(*) → Returns 5 (All rows are counted)

Notes:

• Finding specific conditions:

  • To count employees who joined in 1981: SELECT COUNT(*) FROM EMP WHERE HIREDATE LIKE '1981%';

  • To count employees who joined on a Sunday:

      SELECT COUNT(*)
      FROM EMP
      WHERE DATENAME(DW, HIREDATE) = 'SUNDAY';
    

• Aggregate functions are not allowed in the WHERE clause. They are only allowed in the SELECT and HAVING clauses. Use subqueries to work around this limitation:

  • SELECT ENAME FROM EMP WHERE SAL = (SELECT MAX(SAL) FROM EMP);

To Overcome this problom use other functions or Subqueries:

• Date functions: DATEPART, DATENAME, DATEADD, DATEDIFF, EOMONTH, FORMAT

• Character functions: UPPER, LOWER, LEN, LEFT, RIGHT, SUBSTRING, REPLICATE, REPLACE, TRANSLATE, CHARINDEX

• Numeric functions: ROUND, CEILING, FLOOR

• Conversion functions: CAST, CONVERT

• Special functions: ISNULL

• Analytical functions: RANK, DENSE_RANK, ROW_NUMBER, LAG, LEAD

• Aggregate functions: MAX, MIN, SUM, AVG, COUNT, COUNT(*)

---

CASE STATEMENT:

The CASE statement in SQL is used to implement conditional logic, similar to an IF-ELSE structure. It allows you to return values based on specified conditions. There are two types of CASE statements:

1. Simple CASE

2. Searched CASE

Simple CASE

A simple CASE expression is used when the conditions are based on equality (=) comparisons.

Syntax:

CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    ELSE result
END

Example: Display ENAME and a description based on the JOB:

SELECT ENAME,
       CASE JOB
           WHEN 'CLERK' THEN 'WORKER'-- if JOB = 'CLERK' then display 'WORKER'   
           WHEN 'MANAGER' THEN 'BOSS'
           WHEN 'PRESIDENT' THEN 'BIG BOSS'
           ELSE 'EMPLOYEE'
       END AS JOB_DESCRIPTION
FROM EMP;

Notes:-

The CASE statement must end with the END keyword. This keyword marks the end of the CASE logic and is followed by an alias name, which labels the column in the result set.

AS JOB_DESCRIPTION: This part gives a name (alias) to the column that holds the results of the CASE statement. Here, JOB_DESCRIPTION is the alias for the column that shows the job descriptions based on the conditions.

Without an alias, the resulting column would be unnamed or have a system-generated name, which might not be clear or useful. Giving an alias like JOB_DESCRIPTION makes the output easier to read and understand.

Example: Increment employee salaries based on their department:

UPDATE EMP
SET SAL = CASE DEPTNO
              WHEN 10 THEN SAL + (SAL * 0.1)   -- If DEPTNO = 10, increase SAL by 10%
              WHEN 20 THEN SAL + (SAL * 0.15)  -- If DEPTNO = 20, increase SAL by 15%
              WHEN 30 THEN SAL + (SAL * 0.2)   -- If DEPTNO = 30, increase SAL by 20%
              ELSE SAL + (SAL * 0.05)          -- For other DEPTNOs, increase SAL by 5%
          END;

Searched CASE

A searched CASE expression allows for more complex conditions that aren't necessarily based on equality.

Syntax:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE result
END

Example: Calculate total marks and result for students:

SELECT SNO,
       (S1 + S2 + S3) AS TOTAL,
       (S1 + S2 + S3) / 3 AS AVG,
       CASE
           WHEN S1 >= 35 AND S2 >= 35 AND S3 >= 35 THEN 'PASS'
           ELSE 'FAIL'
       END AS RESULT
FROM STUDENT;

Example: Classify transactions as 'CREDIT' or 'DEBIT' based on the amount:

SELECT AMT,
       CASE
           WHEN AMT >= 0 THEN 'CREDIT'
           ELSE 'DEBIT'
       END AS TRANS
FROM T1;

Out:-

---

GROUP BY Clause

The GROUP BY clause is used to group rows that have the same values in specified columns. This helps in calculating summary statistics like MIN, MAX, SUM, AVG, and COUNT for each group. In other words, the GROUP BY clause groups rows based on one or more columns to calculate MIN, MAX, SUM, AVG, and COUNT for each group.

Example Table: EMP

Using the GROUP BY clause, you can turn detailed data into summarized data for analysis.

Syntax:

SELECT columns
FROM tablename
[WHERE condition]
GROUP BY column1, column2, ...
[HAVING condition]
[ORDER BY column ASC/DESC]

Execution Order:

1. FROM

2. WHERE

3. GROUP BY

4. HAVING

5. SELECT

6. ORDER BY

Example: Display department-wise total salary:

SELECT DEPTNO, SUM(SAL) AS TOTSAL
FROM EMP
GROUP BY DEPTNO;

Output:

Detailed Process:

1. FROM EMP:

   • Retrieves all data from the EMP table.

     | EMPNO | ENAME | SAL | DEPTNO | | --- | --- | --- | --- | | 1 | A | 3000 | 10 | | 2 | B | 4000 | 20 | | 3 | C | 2000 | 30 | | 4 | D | 5000 | 20 | | 5 | E | 4000 | 10 |

2. GROUP BY DEPTNO:

   • Groups rows by department number.

     | DEPTNO | EMPNO | ENAME | SAL | DEPTNO (Grouped) | | --- | --- | --- | --- | --- | | 10 | 1 | A | 3000 | 10 | | | 5 | E | 4000 | 10 | | 20 | 2 | B | 4000 | 20 | | | 4 | D | 5000 | 20 | | 30 | 3 | C | 2000 | 30 |

     This table shows how the data is grouped by the DEPTNO column, with the DEPTNO values repeated for each employee in the same department.

3. SELECT DEPTNO, SUM(SAL):

   • Calculates the total salary for each department.

     | DEPTNO | TOTSAL | | --- | --- | | 10 | 7000 | | 20 | 9000 | | 30 | 2000 |

Q: Display job-wise number of employees?

SELECT JOB, COUNT(*) AS CNT
FROM EMP 
GROUP BY JOB;

Output:

Q: Display year-wise number of employees joined ?

SELECT DATEPART(YY, HIREDATE) AS YEAR, COUNT(*) AS CNT
FROM EMP
GROUP BY DATEPART(YY, HIREDATE);

Output:

Q: Display day wise no of employees joined ?

SELECT DATENAME(DW,HIREDATE) AS DAY,COUNT(*) AS CNT
FROM EMP
GROUP BY DATENAME(DW,HIREDATE)

Q: Display month wise no of employees joined in in the year 1981 ?

SELECT DATENAME(mm,HIREDATE) AS MONTH,COUNT(*) AS CNT
FROM EMP
WHERE DATEPART(YY,HIREDATE)=1981
GROUP BY DATENAME(mm,HIREDATE)

Q: Display departments having more than 3 employees?

SELECT DEPTNO, COUNT(*) AS CNT
FROM EMP
GROUP BY DEPTNO
HAVING COUNT(*) > 3;

Output:

Note: The HAVING clause is used instead of WHERE to filter groups based on aggregate values. In this example, COUNT(*) > 3 cannot be used directly in the WHERE clause because the count is determined after grouping. SQL Server calculates the department-wise count only after grouping, so we apply the condition COUNT(*) > 3 using the HAVING clause.

SELECT DEPTNO,COUNT(*) AS CNT
 FROM EMP
 GROUP BY DEPTNO
 HAVING COUNT(*) > 3

WHERE vs. HAVING:

WHERE Clause:

• Purpose: Filters rows before grouping.

• Usage: Applied to individual rows and used when conditions do not involve aggregate functions.

HAVING Clause:

• Purpose: Filters groups after grouping.

• Usage: Applied to groups and used when conditions involve aggregate functions.

Key Differences:

Q: Display department-wise number of employees where department numbers are 10 or 20, and the number of employees in each department is greater than 3.

SELECT DEPTNO, COUNT(*) AS CNT
FROM EMP
WHERE DEPTNO IN (10, 20)
GROUP BY DEPTNO
HAVING COUNT(*) > 3;

Output:

Explanation:

• The WHERE clause filters rows where DEPTNO is either 10 or 20.

• The GROUP BY clause groups these rows by DEPTNO.

• The HAVING clause filters these groups to retain only those with more than 3 employees.

Q: Find southern states with a population greater than 5 crores (50 million).

SELECT STATE, COUNT(*) AS CNT
FROM PERSONS
WHERE STATE IN ('AP', 'TS', 'KA', 'KL', 'TN')
GROUP BY STATE
HAVING COUNT(*) > 50000000;

Output:

Explanation:

• The WHERE clause filters rows for the states 'AP', 'TS', 'KA', 'KL', and 'TN'.

• The GROUP BY clause groups the data by STATE.

• The HAVING clause filters out states with a population count not exceeding 50 million.

Range grouping:

Range Grouping is a way to categorize data into different ranges or intervals. This is useful when you want to analyze how data falls into various segments, such as salary ranges, age groups, or other numerical categories.

To group employees based on their salary ranges and count the number of employees in each range:

Example of Range Grouping with Salaries

Imagine you have a list of employees and their salaries, and you want to see how many employees fall into different salary ranges, like:

1. 0 to 2000

2. 2001 to 4000

3. Above 4000

You can use SQL to create these categories (or "ranges") and count how many employees are in each range.

SQL Query

Here's a simple SQL query that groups employees by salary ranges:

SELECT 
  CASE
    WHEN SAL BETWEEN 0 AND 2000 THEN '0-2000'
    WHEN SAL BETWEEN 2001 AND 4000 THEN '2001-4000'
    WHEN SAL > 4000 THEN 'ABOVE 4000'
  END AS SALRANGE,
  COUNT(*) AS CNT
FROM EMP
GROUP BY 
  CASE
    WHEN SAL BETWEEN 0 AND 2000 THEN '0-2000'
    WHEN SAL BETWEEN 2001 AND 4000 THEN '2001-4000'
    WHEN SAL > 4000 THEN 'ABOVE 4000'
  END;

Output:

Explanation

• CASE Statement: This is used to specify different conditions. In this case, we are checking if the salary (SAL) falls within certain ranges.

  • WHEN SAL BETWEEN 0 AND 2000 THEN '0-2000': This categorizes salaries from 0 to 2000 into the '0-2000' range.

  • WHEN SAL BETWEEN 2001 AND 4000 THEN '2001-4000': This categorizes salaries from 2001 to 4000 into the '2001-4000' range.

  • WHEN SAL > 4000 THEN 'ABOVE 4000': This categorizes salaries above 4000 into the 'ABOVE 4000' range.

• COUNT(*): This function counts the number of employees in each salary range.

• GROUP BY: This groups the data based on the salary ranges defined in the CASE statement.

Q. Display dept wise and with in dept job wise total salary ?

To display total salaries for each job within each department:

SELECT DEPTNO, JOB, SUM(SAL) AS TOTSAL
FROM EMP
GROUP BY DEPTNO, JOB
ORDER BY DEPTNO ASC;

Output:

Q. Display year wise and with in year quarter wise no of employees ?

To display the number of employees hired each year, broken down by quarter:

SELECT 
  DATEPART(YY, HIREDATE) AS YEAR,
  DATEPART(QQ, HIREDATE) AS QRT,
  COUNT(*) AS CNT
FROM EMP
GROUP BY DATEPART(YY, HIREDATE), DATEPART(QQ, HIREDATE)
ORDER BY YEAR ASC;

Output:

Each row shows the year, quarter, and the number of employees hired during that quarter.

ROLLUP & CUBE:

Both functions are used to display subtotals and grand totals:

• GROUP BY ROLLUP(col1, col2, ...)

• GROUP BY CUBE(col1, col2, ...)

I have a query that calculates the total salary for each department:

SELECT Deptno, sum(sal) AS Sumsal
FROM Emp
GROUP BY Deptno
ORDER BY Deptno ASC;

OUT:-

But if the user wants subtotals and a grand total, we use ROLLUP and CUBE.

ROLLUP:

ROLLUP displays subtotals for each group and also shows the grand total.

• Purpose: Generates subtotals and a grand total for grouped data.

• Syntax: GROUP BY ROLLUP(col1, col2, ...)

Example:-

SELECT Deptno, sum(sal) AS Sumsal
FROM Emp
GROUP BY ROLLUP(Deptno)
ORDER BY Deptno ASC;

Out:-

Grand Total is the total salary paid to all the departments.

CUBE:

CUBE displays subtotals for each group by column (deptno, job) and also shows the grand total.

• Purpose: Provides subtotals for all possible combinations of grouped columns, as well as the grand total.

• Syntax: GROUP BY CUBE(col1, col2, ...)

• Example:-

    SELECT Deptno, sum(sal) AS Sumsal 
    FROM Emp 
    GROUP BY ROLLUP(Deptno) 
    ORDER BY Deptno ASC;
  

  Out:-

  | DEPTNO | SUMSAL | | --- | --- | | 10 | 7450 | | 20 | 14075 | | 30 | 9400 | | 40 | 1300 | | | 32220 => Grand Total |

Then what is the diffrence bitween ROOLUP and CUBE?

If I execute the query, the data is as follows:

SELECT Deptno, job, sum(sal) AS Sumsal 
FROM Emp 
GROUP BY Deptno, job
ORDER BY 1,2;

Out:-

It's calculating the total salary by department and job.

But still user wants subtotals and a grand total.

For example, the subtotal for the Manager in the 10th department is 2450, the subtotal for the President in the 10th department is 5000, and the grand total is 7450 in the 10th department.

Using ROOLUP:

SELECT Deptno, job, sum(sal) AS Sumsal 
FROM Emp 
GROUP BY ROLLUP(Deptno, job)
ORDER BY 1,2;

Out:-

Using CUBE:

SELECT Deptno, job, sum(sal) AS Sumsal 
FROM Emp 
GROUP BY CUBE(Deptno, job)
ORDER BY 1,2;

Out:-

Diffrence bitween ROOLUP and CUBE:-

If the end user sees this table, they might not understand what 32225, 7450, 14075, 9400, and 1300 mean. How will they know what these numbers represent?

That's when SQL provides the function called GROUPING_ID().

GROUPING_ID():

• Purpose: Identifies the level of aggregation for each row in a result set that uses ROLLUP or CUBE.

• Syntax: GROUPING_ID(col1, col2, ...) .

Common Values and Their Meanings:

• 0: Neither column is aggregated (regular row for ROLLUP or CUBE).

• 1: The first column is aggregated (subtotal for the first column if using ROLLUP).

• 2: The second column is aggregated.

• 3: Both columns are aggregated (grand total row for both columns).

For example, with CUBE, we break it down into three totals. A value of 1 means a subtotal for the first group of columns, 2 means a subtotal for the second group of columns, and 3 means the grand total. When we use this, it returns the values 1, 2, or 3.

SELECT Deptno, job, sum(sal) AS Sumsal, 
    GROUPING_ID(Deptno, job) AS subtotals
FROM Emp 
GROUP BY CUBE(Deptno, job)
ORDER BY 1,2;

Out:-

To make more understable:-

SELECT Deptno, job, sum(sal) AS Sumsal, 
    CASE GROUPING_ID(Deptno, job)
        WHEN 0 THEN 'Amount'
        WHEN 1 THEN 'Dept Subtotal'
        WHEN 2 THEN 'Job Subtotal'
        WHEN 3 THEN 'Grand total'
    END AS SUBTOTALS
FROM Emp 
GROUP BY CUBE(Deptno, job)
ORDER BY 1,2;

Out:-

Q. Display state-wise and within state gender-wise population and also display state-wise and gender-wise subtotals?

Q. Display year-wise and within year quarter-wise total amount and also display year-wise subtotals?

Note's:

1. Column Alias in GROUP BY:

SELECT DATEPART(YY, HIREDATE) AS YEAR, COUNT(*) AS CNT
FROM EMP
GROUP BY DATEPART(YY, HIREDATE)  /* valid */
ORDER BY YEAR ASC  /* valid */

• Correction: Use the full expression (DATEPART(YY, HIREDATE)) in the GROUP BY clause instead of the column alias (YEAR), because the GROUP BY clause is processed before the SELECT clause, where aliases are defined.

2. Non-Aggregate Columns in SELECT:

SELECT DEPTNO, COUNT(*) AS CNT
FROM EMP
GROUP BY DEPTNO  /* valid */

• Correction: Only include columns that are part of the GROUP BY clause or are used in aggregate functions in the SELECT statement. Including non-aggregated columns like ENAME without grouping them will result in an error.

GROUP BY Clause: Must include the actual expressions used, not column aliases.

SELECT Clause with GROUP BY: Should only include grouped columns and aggregate functions.

Semmary:-

• Importance ofGROUP BY: Essential for aggregating data by specific columns to perform calculations like totals, averages, and counts for each group.

• Writing Queries UsingGROUP BY: Use it to group data and apply aggregate functions to those groups.

• WHERE vs HAVING: WHERE filters rows before grouping; HAVING filters groups after aggregation.

• Displaying Subtotals & Grand Total: Use ROLLUP for subtotals and grand total; CUBE for subtotals across all possible combinations.

• GROUPING_ID: Identifies the level of aggregation for each row in a result set.

---

Integrity Constraints:

Integrity constraints are rules enforced on database columns to maintain the accuracy, consistency, and reliability of the data stored in the database. These constraints prevent users from entering invalid data and ensure data quality.

• Integrity constraints are rules to maintain data quality and consistency.

• They prevent users from entering invalid data.

• They enforce rules, such as a minimum balance of 1000.

Types of Integrity Constraints

1. NOT NULL:

   • Ensures that a column cannot have a NULL value.

   • Example: Name VARCHAR(100) NOT NULL

2. UNIQUE:

   • Ensures that all values in a column are unique, meaning no two rows can have the same value for this column.

   • Example: Email VARCHAR(255) UNIQUE

3. PRIMARY KEY:

   • A combination of NOT NULL and UNIQUE. It uniquely identifies each record in a table.

   • Example: ID INT PRIMARY KEY

4. CHECK:

   • Ensures that all values in a column satisfy a specific condition.

   • Example: Salary DECIMAL(10, 2) CHECK (Salary >= 1000)

5. FOREIGN KEY:

   • Ensures the referential integrity of the data in one table to match values in another table. It creates a link between the data in two tables.

   • Example: DeptID INT, FOREIGN KEY (DeptID) REFERENCES Department(ID)

6. DEFAULT:

   • Provides a default value for a column when no value is specified.

   • Example: Status VARCHAR(20) DEFAULT 'Active'

Declaring Constraints

Constraints can be declared at two levels:

• Column level

• Table level

1. Column Level:

   • Constraints are declared immediately after the column definition is called column level.

   • Suitable for constraints that apply to individual columns.

Syntax:

    CREATE TABLE TableName (
        ColumnName DataType(Size) CONSTRAINT
        ...
    );

Example:

    CREATE TABLE Employees (
        ID INT PRIMARY KEY,
        Name VARCHAR(100) NOT NULL,
        Email VARCHAR(255) UNIQUE,
        Salary DECIMAL(10, 2) CHECK (Salary >= 1000)
    );

2. Table Level:

   • Constraints are declared after all column definitions.

   • Suitable for constraints that apply to multiple columns or involve relationships between tables.

Syntax:

    CREATE TABLE TableName (
        ColumnName1 DataType(Size),
        ColumnName2 DataType(Size),
        ...
        CONSTRAINT ConstraintName CONSTRAINT_TYPE (Columns)
    );

Example:

    CREATE TABLE Employees (
        ID INT,
        Name VARCHAR(100),
        DeptID INT,
        CONSTRAINT PK_Employee PRIMARY KEY (ID),
        CONSTRAINT FK_Dept FOREIGN KEY (DeptID) REFERENCES Department(ID)
    );

This structured approach helps in maintaining the integrity and reliability of the database by ensuring that data adheres to predefined rules.

NOT NULL:

• The NOT NULL constraint ensures that a column cannot have a NULL value.

• A field declared with NOT NULL is considered a mandatory field.

Example:

CREATE TABLE emp11 (
    empno INT,
    ename VARCHAR(10) NOT NULL
);

INSERT INTO emp11 VALUES (100, NULL); -- ERROR
INSERT INTO emp11 VALUES (101, 'A');

---

UNIQUE:

• The UNIQUE constraint ensures that all values in a column are different.

• A column with the UNIQUE constraint cannot have duplicate values.

Example:

CREATE TABLE cust (
    custid INT,
    cname VARCHAR(10) NOT NULL,
    emailid VARCHAR(20) UNIQUE
);

INSERT INTO cust VALUES (100, 'A', 'abc@gmail.com');
INSERT INTO cust VALUES (101, 'B', 'abc@gmail.com'); -- ERROR
INSERT INTO cust VALUES (102, 'C', NULL);
INSERT INTO cust VALUES (103, 'D', NULL); -- ERROR

Note: Depending on the database system, the behavior of UNIQUE with NULLs may vary. Some databases might allow multiple NULLs in a UNIQUE column.

PRIMARY KEY:

• The PRIMARY KEY constraint uniquely identifies each record in a table.

• A primary key is a combination of UNIQUE and NOT NULL constraints.

• Only one primary key is allowed per table.

• The primary key column(s) must contain unique values and cannot contain NULLs.

Example:

CREATE TABLE emp13 (
    empid INT PRIMARY KEY,
    ename VARCHAR(10) NOT NULL
);

INSERT INTO emp13 VALUES (100, 'A');
INSERT INTO emp13 VALUES (100, 'B'); -- ERROR
INSERT INTO emp13 VALUES (NULL, 'C'); -- ERROR

Only one primary key is allowed per table. If you need multiple unique identifiers, declare one column as the primary key and the other columns with the UNIQUE and NOT NULL constraints.

Multiple Unique Columns Example:

CREATE TABLE cust (
    custid INT PRIMARY KEY,
    cname VARCHAR(10) NOT NULL,
    aadharno NUMERIC(12) UNIQUE NOT NULL,
    panno CHAR(10) UNIQUE NOT NULL
);

Difference between UNIQUE and PRIMARY KEY:

This explanation helps to understand the purpose and behavior of the NOT NULL, UNIQUE, and PRIMARY KEY constraints in database tables, ensuring data integrity and uniqueness where necessary.

Candidate Key:

• A candidate key is a field or combination of fields that can uniquely identify a record in a table and can be chosen as the primary key.

• If a table has multiple candidate keys, one is selected as the primary key, and the others can be used as secondary or alternate keys.

• A field eligible for primary key is called candidate key.

Example:

Table: VEHICLES

VEHNO	NAME	MODEL	COST	CHASSISNO
1	Car A	X1	10000	123ABC
2	Car B	X2	15000	456DEF

• Candidate keys: VEHNO, CHASSISNO

• Primary key: VEHNO

• Secondary key (or alternate key): CHASSISNO

When creating a table, secondary key columns are declared with UNIQUE NOT NULL.

CHECK Constraint:

• The CHECK constraint ensures that all values in a column meet a specific condition.

Syntax:

CHECK (condition)

Examples:

1. Salary must be a minimum of 3000:

CREATE TABLE emp15 (
    empid INT PRIMARY KEY,
    ename VARCHAR(10) NOT NULL,
    sal MONEY CHECK(sal >= 3000)
);

INSERT INTO emp15 VALUES (100, 'A', 1000); -- ERROR
INSERT INTO emp15 VALUES (101, 'B', 5000);
INSERT INTO emp15 VALUES (102, 'C', NULL); -- CHECK constraint allows NULLs

2. Gender must be 'm' or 'f':

gender CHAR(1) CHECK (gender IN ('m', 'f'))

3. Amount must be a multiple of 100:

amt MONEY CHECK (amt % 100 = 0)

4. Password must be a minimum of 6 characters:

pwd VARCHAR(10) CHECK (LEN(pwd) >= 6)

5. Email ID must contain '@' and end with '.com', '.co', or '.in':

emailid VARCHAR(20) CHECK (emailid LIKE '%@%' AND 
                          (emailid LIKE '%.com' OR 
                           emailid LIKE '%.co' OR 
                           emailid LIKE '%.in'))

**Q:**Password must:

• • Contain lowercase alphabets

    * Contain uppercase alphabets

    * Contain digits

    * Contain _, @, or $

CREATE TABLE users (
    userid INT PRIMARY KEY,
    pwd VARCHAR(20) CHECK (
        pwd LIKE '%[a-z]%' AND 
        pwd LIKE '%[A-Z]%' AND 
        pwd LIKE '%[0-9]%' AND 
        (pwd LIKE '%[_]%' OR pwd LIKE '%@%' OR pwd LIKE '%$%')
    )
);

FOREIGN KEY:

• A foreign key is used to establish a relationship between two tables.

Example:

Before normalization, the table EMP contains redundancy:

EMPNO | ENAME | SAL  | DNO | DNAME | LOC
----------------------------------------
1     | A     | 3000 | 10  | HR    | BLR
2     | B     | 3000 | 10  | HR    | BLR
3     | C     | 3000 | 10  | HR    | BLR

The above table contains redundancy. To reduce redundancy, divide the table into two tables and establish a relationship by adding a foreign key.

To establish the relationship, take the primary key of one table and add it to the other table as a foreign key, declaring it with a references constraint.

Tables after normalization:

DEPT:

DNO | DNAME | LOC
-----------------
10  | HR    | BLR
20  | IT    | HYD

EMP:

EMPNO | ENAME | SAL  | DNO
--------------------------
1     | A     | 4000 | 10
2     | B     | 3000 | 20
3     | C     | 5000 | 90 => INVALID (90 is not in DEPT)
4     | D     | 3000 | 10
5     | E     | 2000 | NULL

Key Points:

• To establish a relationship, take the primary key of one table (e.g., DEPT.DNO) and add it to another table (e.g., EMP.DNO) as a foreign key with the REFERENCES constraint.

• The values entered in the foreign key column (EMP.DNO) should match values in the primary key column (DEPT.DNO).

• Foreign keys allow duplicates and null values.

• Declaring a foreign key creates a parent/child relationship between two tables:

  • The table with the primary key is the parent (e.g., DEPT).

  • The table with the foreign key is the child (e.g., EMP).

SQL Example:

Creating DEPT table:

CREATE TABLE DEPT55 (
    DNO INT PRIMARY KEY, --Primary key
    DNAME VARCHAR(10) UNIQUE NOT NULL
);
INSERT INTO DEPT55 VALUES (10, 'HR'), (20, 'IT');

Creating EMP table:

CREATE TABLE EMP55 (
    EMPNO INT PRIMARY KEY,
    ENAME VARCHAR(10) NOT NULL,
    SAL MONEY CHECK(SAL >= 3000),
    DNO INT REFERENCES DEPT55(DNO)-- Foreign key
);

Inserting data into EMP table:

INSERT INTO EMP55 VALUES (1, 'A', 4000, 10);
INSERT INTO EMP55 VALUES (2, 'B', 3000, 90); -- ERROR: 90 is not in DEPT55.DNO
INSERT INTO EMP55 VALUES (3, 'C', 3000, 10);
INSERT INTO EMP55 VALUES (4, 'D', 5000, NULL); -- NULL is allowed

This corrected explanation clearly outlines how to establish relationships between tables using the foreign key constraint and includes relevant examples to demonstrate the concept.

Relationship Types:

1. One-to-One

2. One-to-Many

3. Many-to-One

4. Many-to-Many

• Default relationship in SQL Server: One-to-Many

One-to-One Relationship:

• To establish a one-to-one relationship, declare the foreign key with a unique constraint.

Example:

DEPT Table:

DNO | DNAME
-----------
10  | HR
20  | IT

MGR Table:

MGRNO | MNAME | DNO
-------------------
1     | A     | 10
2     | B     | 20

SQL Example:

Creating the DEPT table:

CREATE TABLE DEPT (
    DNO INT PRIMARY KEY,
    DNAME VARCHAR(10) NOT NULL
);
INSERT INTO DEPT VALUES (10, 'HR'), (20, 'IT');

Creating the MGR table:

CREATE TABLE MGR (
    MGRNO INT PRIMARY KEY,
    MNAME VARCHAR(10) NOT NULL,
    DNO INT UNIQUE REFERENCES DEPT(DNO)
);

Inserting data intoMGR table:

INSERT INTO MGR VALUES (1, 'A', 10);
INSERT INTO MGR VALUES (2, 'B', 20);

Key Points:

• The DNO column in the MGR table is both a foreign key and unique. This ensures that each department (DEPT.DNO) is linked to exactly one manager (MGR.DNO), creating a one-to-one relationship.

• The DNO column in the MGR table references the DNO column in the DEPT table and must be unique to keep the one-to-one relationship.

This explanation shows how to create a one-to-one relationship using a foreign key and a unique constraint, with examples to illustrate the concept.

Many-to-Many Relationship:

• To establish a many-to-many relationship, create a third table that includes the primary keys of both tables as foreign keys.

Example:

CUST Table:

CID | NAME | ADDR
-----------------
1   | A    | HYD
2   | B    | BLR

PRODUCTS Table:

PRODID | PNAME | PRICE
---------------------
100    | A     | 500
101    | B     | 200

SALES Table:

CID | PRODID | QTY
------------------
1   | 100    | 1
1   | 101    | 2
2   | 100    | 1
2   | 101    | 3

SQL Example:

Creating the CUST table:

CREATE TABLE CUST (
    CID INT PRIMARY KEY,
    NAME VARCHAR(10),
    ADDR VARCHAR(20)
);
INSERT INTO CUST VALUES (1, 'A', 'HYD'), (2, 'B', 'BLR');

Creating the PRODUCTS table:

CREATE TABLE PRODUCTS (
    PRODID INT PRIMARY KEY,
    PNAME VARCHAR(10),
    PRICE MONEY
);
INSERT INTO PRODUCTS VALUES (100, 'A', 500), (101, 'B', 200);

Creating the SALES table:

CREATE TABLE SALES (
    CID INT,
    PRODID INT,
    QTY INT,
    FOREIGN KEY (CID) REFERENCES CUST(CID),
    FOREIGN KEY (PRODID) REFERENCES PRODUCTS(PRODID),
    PRIMARY KEY (CID, PRODID)
);
INSERT INTO SALES VALUES (1, 100, 1), (1, 101, 2), (2, 100, 1), (2, 101, 3);

DEFAULT:

• A column can be declared with a default value as follows.

Example:

CREATE TABLE emp16 (
    empno INT PRIMARY KEY,
    ename VARCHAR(10) NOT NULL,
    hiredate DATE DEFAULT GETDATE()
);
INSERT INTO emp16 (empno, ename) VALUES (100, 'A');
INSERT INTO emp16 VALUES (101, 'B', '2023-01-01');
INSERT INTO emp16 VALUES (102, 'C', NULL);

Out:-
EMPNO | ENAME | HIREDATE
------------------------
100   | A     | 2023-10-06
101   | B     | 2023-01-01
102   | C     | NULL

Table Level:

• Use table level to declare constraints for multiple columns or combinations of columns.

• Table-level constraints are declared after all columns are defined.

Declaring Check Constraint at Table Level:

PRODUCTS Table:

prodid | pname | manufacture_dt| expiry_dt
-----------------------------------------------------
100    | A     | 2023-10-01    | 2023-01-01 (INVALID)

Rule: expiry_dt > manufacture_dt

SQL Example:

CREATE TABLE PRODUCTS (
    prodid INT PRIMARY KEY,
    pname VARCHAR(10) NOT NULL,
    manufacture_dt DATE,
    expiry_dt DATE,
    CHECK (expiry_dt > manufacture_dt)
);
INSERT INTO PRODUCTS VALUES (100, 'A', GETDATE(), '2023-01-01'); -- ERROR
INSERT INTO PRODUCTS VALUES (101, 'B', '2023-01-01', GETDATE()); -- 1 row affected

Composite Primary Key:

• If a combination of columns declared as primary key, it is called a composite primary key.

• In some tables, a combination of columns is needed to uniquely identify records, and that combination is declared as a primary key at the table level.

Example:

STUDENT Table:

SID | SNAME
-----------
1   | A
2   | B

COURSE Table:

CID | CNAME
-----------
10  | .NET
11  | SQL

REGISTRATIONS Table:

SID | CID | DOR        | FEE
------------------------------
1   | 10  | 2023-10-07 | 1000
1   | 11  | 2023-10-07 | 1000
2   | 10  | 2023-10-07 | 1000
1   | 10  | 2023-10-07 | 1000 (ERROR)

SQL Example:

Creating the STUDENT table:

CREATE TABLE STUDENT (
    SID INT PRIMARY KEY,
    SNAME VARCHAR(10) NOT NULL
);
INSERT INTO STUDENT VALUES (1, 'A'), (2, 'B');

Creating the COURSE table:

CREATE TABLE COURSE (
    CID INT PRIMARY KEY,
    CNAME VARCHAR(10) NOT NULL
);
INSERT INTO COURSE VALUES (10, '.NET'), (11, 'SQL');

Creating the REGISTRATIONS table:

CREATE TABLE REGISTRATIONS (
    SID INT REFERENCES STUDENT(SID),
    CID INT REFERENCES COURSE(CID),
    DOR DATE,
    FEE MONEY,
    PRIMARY KEY (SID, CID)
);
INSERT INTO REGISTRATIONS VALUES (1, 10, GETDATE(), 1000);
INSERT INTO REGISTRATIONS VALUES (1, 11, GETDATE(), 1000);
INSERT INTO REGISTRATIONS VALUES (2, 10, GETDATE(), 1000);

Question:

SALES Table:

DATEID    | PRODID | CUSTID | QTY | AMT
--------------------------------------
2023-10-05 | 100    | 10     | 1   | 1000
2023-10-05 | 100    | 11     | 1   | 1000
2023-10-05 | 101    | 10     | 1   | 2000
2023-10-06 | 100    | 10     | 1   | 1000

Identify the primary key and write the create table script.

Answer:

CREATE TABLE SALES (
    DATEID DATE,
    PRODID INT,
    CUSTID INT,
    QTY INT,
    AMT MONEY,
    PRIMARY KEY (DATEID, PRODID, CUSTID)
);

Composite Foreign Key:

• If a combination of columns declared as a foreign key, it is called a composite foreign key.

• A composite foreign key refers to a composite primary key.

Example:

REGISTRATIONS Table:

SID | CID | DOR        | FEE
------------------------------
1   | 10  | 2023-10-07 | 1000
1   | 11  | 2023-10-07 | 1000
2   | 10  | 2023-10-07 | 1000

CERTIFICATES Table:

CERTNO | DOI      | SID | CID
-----------------------------
1000   | 2023-10-07 | 1   | 10
1001   | 2023-10-07 | 1   | 11
1002   | 2023-10-07 | 2   | 11

In the above table SID,CID combination should match with registrations table SID,CID combination.

SQL Example:

Creating the REGISTRATIONS table:

CREATE TABLE REGISTRATIONS (
    SID INT REFERENCES STUDENT(SID),
    CID INT REFERENCES COURSE(CID),
    DOR DATE,
    FEE MONEY,
    PRIMARY KEY (SID, CID)
);

Creating the CERTIFICATES table:

CREATE TABLE CERTIFICATES (
    CERTNO INT PRIMARY KEY,
    DOI DATE,
    SID INT,
    CID INT,
    FOREIGN KEY (SID, CID) REFERENCES REGISTRATIONS(SID, CID)
);
INSERT INTO CERTIFICATES VALUES (1000, GETDATE(), 2, 11); -- ERROR

Q. Which of the following constraints cannot be declared at the table level?

A. UNIQUE

B.) CHECK

C.) NOT NULL

D.) PRIMARY KEY

E.) FOREIGN KEY

Answer: C

Q. Which statements are true regarding constraints?

A.) A foreign key cannot contain NULL value - F

B.) A column with UNIQUE constraint can contain NULL value - T

C.) A constraint is enforced only for the INSERT operation on a table - F

D.) All constraints can be defined at the column level and table level - F

Q. Which CREATE TABLE statement is valid?

A.)

CREATE TABLE ord_details (
    ord_no NUMERIC(2) PRIMARY KEY,
    item_no NUMERIC(3) PRIMARY KEY,
    ord_date DATE NOT NULL
);

B.)

CREATE TABLE ord_details (
    ord_no NUMERIC(2) UNIQUE, NOT NULL,
    item_no NUMERIC(3),
    ord_date DATE DEFAULT GETDATE() NOT NULL
);

C.)

CREATE TABLE ord_details (
    ord_no NUMERIC(2),
    item_no NUMERIC(3),
    ord_date DATE DEFAULT NOT NULL,
    UNIQUE (ord_no),
    PRIMARY KEY (ord_no)
);

D.)

CREATE TABLE ord_details (
    ord_no NUMERIC(2),
    item_no NUMERIC(3),
    ord_date DATE DEFAULT GETDATE() NOT NULL,
    PRIMARY KEY (ord_no, item_no)
);

Answer: D

Adding Constraints to Existing Table:

Using the ALTER Command to Add Constraints to an Existing Table

CREATE TABLE EMP33
(
    EMPNO INT,
    ENAME VARCHAR(10),
    SAL MONEY,
    DNO INT,
    EMAILID VARCHAR(20)
);

Adding a Primary Key

• Primary key cannot be added to a nullable column.

• To add a primary key, first change the column to NOT NULL.

Step 1: Change the column to NOT NULL

ALTER TABLE EMP33
ALTER COLUMN EMPNO INT NOT NULL;

Step 2: Add the primary key

ALTER TABLE EMP33
ADD PRIMARY KEY (EMPNO);

Adding a Check Constraint

• Add a check constraint to ensure SAL >= 3000.

ALTER TABLE EMP33
ADD CHECK (SAL >= 3000);

Note: The command below returns an error if existing data violates the constraint (above command returns error because in table some of the employee salaries are less than 3000 . While adding constraint sql server also validates existing data).

ALTER TABLE EMP
ADD CHECK (SAL >= 3000); -- ERROR

Solution: Add the check constraint with WITH NOCHECK to avoid validation of existing data.

WITH NOCHECK:

If a check constraint is added "WITH NOCHECK," SQL Server will not validate existing data; it will only validate new data.

ALTER TABLE EMP
WITH NOCHECK
ADD CHECK (SAL >= 3000);

Adding a Foreign Key

• Add a foreign key to DNO that references the primary key DNO of DEPT55 table.

ALTER TABLE EMP33
ADD FOREIGN KEY (DNO) REFERENCES DEPT55 (DNO);

Adding a Unique Constraint

• Add a unique constraint to EMAILID.

ALTER TABLE EMP33
ADD UNIQUE (EMAILID);

Changing from NULL to NOT NULL

• Modify the column ENAME to NOT NULL.

ALTER TABLE EMP33
ALTER COLUMN ENAME VARCHAR(10) NOT NULL;

Dropping Constraints

• Drop a constraint using the following syntax:

ALTER TABLE <tabname>
DROP CONSTRAINT <name>;

Example: Drop a check constraint in EMP33.

ALTER TABLE EMP33
DROP CONSTRAINT CK__EMP33__SAL__693CA210;

Example: Drop the primary key in the DEPT55 table.

ALTER TABLE DEPT55
DROP CONSTRAINT PK__DEPT55__C035B8C2CC99E6C6; -- ERROR

DROP TABLE DEPT55 => ERROR

TRUNCATE TABLE DEPT55 => ERROR

Note:

• A primary key cannot be dropped if referenced by some foreign key.

• The primary key table cannot be dropped if it referenced by some foreign key.

• The primary key table cannot be truncated if referenced by some foreign key.

DELETE Rules

Declaring DELETE Rules with Foreign Keys

1. ON DELETE NO ACTION (DEFAULT)

2. ON DELETE CASCADE

3. ON DELETE SET NULL

4. ON DELETE SET DEFAULT

• These rules are declared with foreign keys.

• DELETE rules specify how child rows are affected when a parent row is deleted.

ON DELETE NO ACTION

• Parent row cannot be deleted if associated with child rows.

CREATE TABLE DEPT88
(
    DNO INT PRIMARY KEY,
    DNAME VARCHAR(10) UNIQUE NOT NULL
);

INSERT INTO DEPT88 VALUES (10, 'HR'), (20, 'IT');

CREATE TABLE EMP88
(
    EMPNO INT PRIMARY KEY,
    ENAME VARCHAR(10) NOT NULL,
    DNO INT REFERENCES DEPT88(DNO)
);

INSERT INTO EMP88 VALUES (1, 'A', 10), (2, 'B', 10);

DELETE FROM DEPT88 WHERE DNO = 10; -- ERROR

Scenario:

• ACCOUNTS Table

• LOANS Table

Rule: Account closing is not possible if associated with loans.

ON DELETE CASCADE

• Parent row is deleted along with child rows.

CREATE TABLE DEPT88
(
    DNO INT PRIMARY KEY,
    DNAME VARCHAR(10) UNIQUE NOT NULL
);

INSERT INTO DEPT88 VALUES (10, 'HR'), (20, 'IT');

CREATE TABLE EMP88
(
    EMPNO INT PRIMARY KEY,
    ENAME VARCHAR(10) NOT NULL,
    DNO INT REFERENCES DEPT88(DNO) ON DELETE CASCADE
);

INSERT INTO EMP88 VALUES (1, 'A', 10), (2, 'B', 10);

DELETE FROM DEPT88 WHERE DNO = 10; -- 1 ROW AFFECTED

SELECT * FROM EMP88; -- NO ROWS

Scenario:

• ACCOUNTS Table

• TRANS Table

ON DELETE SET NULL

• Parent row is deleted but child rows are not deleted; foreign key will be set to NULL.

CREATE TABLE DEPT88
(
    DNO INT PRIMARY KEY,
    DNAME VARCHAR(10) UNIQUE NOT NULL
);

INSERT INTO DEPT88 VALUES (10, 'HR'), (20, 'IT');

CREATE TABLE EMP88
(
    EMPNO INT PRIMARY KEY,
    ENAME VARCHAR(10) NOT NULL,
    DNO INT REFERENCES DEPT88(DNO) ON DELETE SET NULL
);

INSERT INTO EMP88 VALUES (1, 'A', 10), (2, 'B', 10);

DELETE FROM DEPT88 WHERE DNO = 10; -- 1 ROW AFFECTED

SELECT * FROM EMP88;
-- Result:
-- ENO | ENAME | DNO
-- ----|-------|-----
-- 1   | A     | NULL
-- 2   | B     | NULL

Scenario:

• PROJECTS Table

• EMP Table

Rule: If the project is completed (deleted), set the employee PROJID to NULL.

ON DELETE SET DEFAULT

• Parent row is deleted but child rows are not deleted; foreign key will be set to default value.

CREATE TABLE DEPT88
(
    DNO INT PRIMARY KEY,
    DNAME VARCHAR(10) UNIQUE NOT NULL
);

INSERT INTO DEPT88 VALUES (10, 'HR'), (20, 'IT');

CREATE TABLE EMP88
(
    EMPNO INT PRIMARY KEY,
    ENAME VARCHAR(10) NOT NULL,
    DNO INT DEFAULT 20 REFERENCES DEPT88(DNO) ON DELETE SET DEFAULT
);

INSERT INTO EMP88 VALUES (1, 'A', 10), (2, 'B', 10);

---

Summary

Importance of Constraints

• Constraints ensure data integrity and consistency.

Types of Constraints

• Primary Key

• Foreign Key

• Unique

• Not Null

• Check

• Default

Declaring Constraints

• Column Level

  • Declared within the column definition.

• Table Level

  • Declared after all columns are defined.

Adding Constraints

• Use ALTER TABLE to add constraints to existing tables.

Dropping Constraints

• Use ALTER TABLE <tabname> DROP CONSTRAINT <name> to remove constraints.

DELETE Rules

• ON DELETE NO ACTION: Prevent deletion if child rows exist.

• ON DELETE CASCADE: Delete child rows when parent row is deleted.

• ON DELETE SET NULL: Set foreign key to NULL when parent row is deleted.

• ON DELETE SET DEFAULT: Set foreign key to default value when parent row is deleted.

---

JOINS: