SQL (Structured Query Language) is the language used to interact with relational databases.
Below
are examples of different types of SQL queries commonly used to perform tasks like retrieving data,
modifying data, and managing the database structure.
1. SELECT Query
The SELECT statement is used to retrieve data from a database.
Example: Retrieve all records from a table
sql
Copy code
SELECT * FROM Employees;
Example: Retrieve specific columns from a table
sql
Copy code
SELECT EmployeeID, EmployeeName, Department FROM Employees;
Example: Retrieve records with a condition
sql
Copy code
SELECT EmployeeName, Department FROM Employees WHERE Department = 'HR';
Example: Retrieve distinct values
sql
Copy code
SELECT DISTINCT Department FROM Employees;
Example: Using ORDER BY to sort the results
sql
Copy code
SELECT EmployeeName, Department FROM Employees ORDER BY EmployeeName ASC;
2. INSERT Query
The INSERT INTO statement is used to add new records to a table.
Example: Insert a new record into the Employees table
sql
Copy code
INSERT INTO Employees (EmployeeID, EmployeeName, Department)
VALUES (101, 'John Doe', 'HR');
3. UPDATE Query
The UPDATE statement is used to modify existing records in a table.
Example: Update a record in the Employees table
sql
Copy code
UPDATE Employees
SET Department = 'Marketing'
WHERE EmployeeID = 101;
4. DELETE Query
The DELETE statement is used to remove records from a table.
Example: Delete a specific record from the Employees table
sql
Copy code
DELETE FROM Employees WHERE EmployeeID = 101;
5. JOIN Query
A JOIN statement is used to combine rows from two or more tables based on a related column.
Example: Inner join between two tables (Employees and Departments)
sql
Copy code
SELECT Employees.EmployeeName, Departments.DepartmentName
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;
6. GROUP BY Query
The GROUP BY statement is used to group rows with the same values and often used with aggregate
functions like COUNT(), SUM(), AVG(), MAX(), MIN().
Example: Count employees in each department
sql
Copy code
SELECT Department, COUNT(EmployeeID) AS NumberOfEmployees
FROM Employees
GROUP BY Department;
7. HAVING Query
The HAVING clause is used to filter records after a GROUP BY, similar to WHERE, but for aggregate
functions.
Example: Find departments with more than 5 employees
sql
Copy code
SELECT Department, COUNT(EmployeeID) AS NumberOfEmployees
FROM Employees
GROUP BY Department
HAVING COUNT(EmployeeID) > 5;
8. Aggregate Functions
SQL has various aggregate functions used to perform calculations on data.
COUNT(): Counts the number of rows
SUM(): Adds up values
AVG(): Calculates the average
MAX(): Returns the maximum value
MIN(): Returns the minimum value
Example: Get the total number of employees
sql
Copy code
SELECT COUNT(*) FROM Employees;
Example: Calculate the average salary
sql
Copy code
SELECT AVG(Salary) FROM Employees;
9. Subqueries
A subquery is a query nested inside another query.
Example: Select employees with the highest salary
sql
Copy code
SELECT EmployeeName, Salary
FROM Employees
WHERE Salary = (SELECT MAX(Salary) FROM Employees);
10. ALTER TABLE Query
The ALTER TABLE statement is used to modify an existing table.
Example: Add a new column
sql
Copy code
ALTER TABLE Employees ADD Email VARCHAR(100);
Example: Modify an existing column
sql
Copy code
ALTER TABLE Employees MODIFY Salary DECIMAL(10, 2);
Example: Drop a column
sql
Copy code
ALTER TABLE Employees DROP COLUMN Email;
11. CREATE TABLE Query
The CREATE TABLE statement is used to create a new table in the database.
Example: Create a new table called Employees
sql
Copy code
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
EmployeeName VARCHAR(100),
Department VARCHAR(50),
Salary DECIMAL(10, 2)
);
12. DROP TABLE Query
The DROP TABLE statement is used to delete a table completely.
Example: Drop the Employees table
sql
Copy code
DROP TABLE Employees;
13. UNION Query
The UNION operator is used to combine the result sets of two or more SELECT statements. Duplicate
records are removed by default.
Example: Combine two SELECT statements
SELECT EmployeeName FROM Employees
UNION
SELECT ManagerName FROM Managers;
14. LIMIT / TOP Query
The LIMIT (or TOP in some databases) is used to specify the number of records to return.
Example: Retrieve the first 5 records
sql
Copy code
SELECT * FROM Employees LIMIT 5;
These examples provide a basic understanding of SQL queries for various database operations. SQL is
highly versatile, and advanced features like window functions, stored procedures, and triggers allow
even more powerful querying and database management.
Normalization in DBMS
Normalization in DBMS (Database Management Systems) refers to the process of organizing data in a
database to minimize redundancy and avoid undesirable characteristics like insertion, update, and
deletion anomalies. The goal is to divide larger tables into smaller, related tables and ensure data
dependencies are logical.
There are several normal forms in DBMS, each representing a level of normalization. Below are the
different types of normalization with examples.
1. First Normal Form (1NF)
Description: A table is in 1NF if:
o It contains only atomic (indivisible) values.
o Each column contains values of a single type.
o Each record is unique.
Example (Non-1NF Table):
StudentID | StudentName | Courses
----------------------------------------
1 | John | Math, Physics
2 | Alice | Chemistry, Biology
Normalized to 1NF:
StudentID | StudentName | Course
-------------------------------
1 John Math
1 | John | Physics
2 | Alice | Chemistry
2 | Alice | Biology
Explanation: The courses were stored in a single cell, which is a violation of 1NF. After
normalization, each course is represented as a separate row.
2. Second Normal Form (2NF)
Description: A table is in 2NF if:
o It is in 1NF.
o All non-key attributes are fully functionally dependent on the entire primary key (i.e.,
no partial dependency).
Example (Non-2NF Table):
StudentID | CourseID | StudentName | Instructor
------------------------------------------------
1 | 101 | John | Dr. Smith
1 | 102 | John | Dr. Jones
2 | 103 | Alice | Dr. Green
Normalized to 2NF:
Student Table:
StudentID | StudentName
-----------------------
1 | John
2 | Alice
Course Table:
CourseID | CourseName | Instructor
-------------------------------------
101 | Math | Dr. Smith
102 | Physics | Dr. Jones
103 | Chemistry | Dr. Green
Explanation: The non-key attribute StudentName was dependent on just StudentID, causing
partial dependency. We split the table into two, separating student information from course
details.
3. Third Normal Form (3NF)
Description: A table is in 3NF if:
o It is in 2NF.
o No transitive dependency exists (i.e., non-key attributes should not depend on other
non-key attributes).
Example (Non-3NF Table):
StudentID | CourseID | CourseName | Instructor | InstructorPhone
-----------------------------------------------------------
1 | 101 | Math | Dr. Smith | 123-456-7890
2 | 102 | Physics | Dr. Jones | 987-654-3210
StudentCourse Table:
StudentID | CourseID
--------------------
1 | 101
2 | 102
Course Table:
CourseID | CourseName | Instructor
-------------------------------------
101 | Math | Dr. Smith
102 | Physics | Dr. Jones
Instructor Table:
Instructor | InstructorPhone
----------------------------
Dr. Smith | 123-456-7890
Dr. Jones | 987-654-3210
StudentCourse Table:
StudentID | CourseID
--------------------
1 | 101
2 | 102
Course Table:
CourseID | CourseName | Instructor
-------------------------------------
101 | Math | Dr. Smith
102 | Physics | Dr. Jones
Instructor Table:
Instructor | InstructorPhone
----------------------------
Dr. Smith | 123-456-7890
Dr. Jones | 987-654-3210
4. Boyce-Codd Normal Form (BCNF)
Description: A stronger version of 3NF. A table is in BCNF if:
o It is in 3NF.
o For every non-trivial functional dependency X → Y, X must be a superkey.
Example (Non-BCNF Table):
CourseID | Instructor | Room
---------------------------
101 | Dr. Smith | Room 101
102 | Dr. Smith | Room 102
103 | Dr. Jones | Room 103
Issue: The functional dependency Instructor → Room exists, but Instructor is not a superkey because
CourseID uniquely identifies rows.
Normalized to BCNF:
Course Table:
CourseID | Instructor
---------------------
101 | Dr. Smith
102 | Dr. Smith
103 | Dr. Jones
InstructorRoom Table:
Instructor | Room
-----------------
Dr. Smith | Room 101
Dr. Jones | Room 103
Explanation: The dependency Instructor → Room violated BCNF because Instructor was not
a superkey. By splitting the table, we ensure that each non-key dependency relates directly
to a superkey.
5. Fourth Normal Form (4NF)
Description: A table is in 4NF if:
o It is in BCNF.
o It contains no multi-valued dependencies (i.e., no attribute should have multiple
independent values for a single key).
Example (Non-4NF Table):
StudentID | CourseID | Hobby
----------------------------
1 | 101 | Soccer
1 | 101 | Music
2 | 102 | Painting
2 | 102 | Photography
Issue: A student can have multiple hobbies, creating a multi-valued dependency between StudentID
and Hobby.
Normalized to 4NF:
StudentCourse Table:
StudentID | CourseID
--------------------
1 | 101
2 | 102
StudentHobby Table:
StudentID | Hobby
-----------------
1 | Soccer
1 | Music
2 | Painting
2 | Photography
Explanation: We separated hobbies from courses to eliminate the multi-valued dependency.
6. Fifth Normal Form (5NF)
Description: A table is in 5NF if:
o It is in 4NF.
o It cannot be further decomposed into smaller tables without losing information or
introducing redundancy.
Example: Consider a table where multiple projects, suppliers, and parts are involved in a
relationship.
Non-5NF Table:
ProjectID | SupplierID | PartID
------------------------------
P1 | S1 | PA
P1 | S1 | PB
P1 | S2 | PA
Normalized to 5NF (Decomposing to multiple tables):
ProjectSupplier Table:
ProjectID | SupplierID
----------------------
P1 | S1
P1 | S2
SupplierPart Table:
SupplierID | PartID
-------------------
S1 | PA
S1 | PB
S2 | PA
Explanation: We decomposed the table into two smaller tables, ensuring no data
redundancy and preserving the original information.
Summary of Normalization Forms:
1. 1NF: No repeating groups; atomic values only.
2. 2NF: No partial dependency on a composite primary key.
3. 3NF: No transitive dependencies.
4. BCNF: Every determinant must be a superkey.
5. 4NF: No multi-valued dependencies.
6. 5NF: No join dependencies; the table cannot be decomposed further without losing
information.
Normalization ensures that databases are efficient, organized, and free from redundant data,
reducing the chances of anomalies during data manipulation.
