Class notes 22scccc4a

UNIT III- SQL
1. Overview of the SQL Query Language
SQL is used to create, manipulate, retrieve, and control access to databases.

SQL is a standard language for managing relational databases.

 It includes commands for data definition (DDL), manipulation (DML), query
(DQL), control (DCL), and transactions (TCL).
 SQL queries use the SELECT-FROM-WHERE structure to retrieve data.
 It is used in various database management systems like MySQL, PostgreSQL, SQL
Server, and Oracle.
 SQL allows data retrieval, modification, and security enforcement in databases.

1. Data Definition Language (DDL) – CREATE, ALTER, DROP
2. Data Manipulation Language (DML) – INSERT, UPDATE, DELETE
3. Data Query Language (DQL) – SELECT
4. Data Control Language (DCL) – GRANT, REVOKE
5. Transaction Control Language (TCL) – COMMIT, ROLLBACK, SAVEPOINT

1. CREATE TABLE – Defining a New Table
Query:

CREATE TABLE employees (
emp_id INT PRIMARY KEY,
name VARCHAR(50),
salary DECIMAL(10,2),
department VARCHAR(50)
);

Table: employees (After Creation)
emp_i name salary department
d
- - - -



2. ALTER TABLE – Modifying an Existing Table
Adding a New Column (age)

ALTER TABLE employees ADD COLUMN age INT;

Explanation:

 ALTER TABLE employees specifies the table to modify.
 ADD COLUMN age INT adds a new column age of integer type.

,Table: employees (After Alteration)
emp_i name salary departmen age
d t
- - - - -



3. DROP TABLE – Deleting a Table Permanently
Query:

DROP TABLE employees;

Explanation:

 DROP TABLE employees removes the employees table along with all its data.
 This action cannot be undone.




2.SQL Data Definition (DDL)
Data Definition Language (DDL) in SQL is used to define and modify the structure of database
objects such as tables, indexes, constraints, and schemas. DDL statements include CREATE,
ALTER, and DROP, which modify the database structure rather than the data itself.


1. CREATE TABLE – Defining a New Table
The CREATE TABLE statement is used to define the structure of a table by specifying column
names, data types, and constraints.

Example: Creating the employees Table
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
name VARCHAR(50),
salary DECIMAL(10,2)
);

Table: employees (After Creation)
emp_id name salary
- - -



2. ALTER TABLE – Modifying an Existing Table
The ALTER TABLE command allows modifications, such as adding, deleting, or changing
columns.

, Example: Adding a New Column (department)
ALTER TABLE employees ADD COLUMN department VARCHAR(50);

Table: employees (After Alteration)
emp_i name salary department
d
- - - -



3. DROP TABLE – Deleting a Table Permanently
The DROP TABLE statement removes the table and all its data permanently.

Example: Dropping the employees Table
DROP TABLE employees;



3. Basic Structure of SQL Queries
1. Queries follow the SELECT-FROM-WHERE syntax to filter data.
2. SELECT * retrieves all columns from a table, while SELECT column_name fetches
specific columns.
3. The WHERE clause applies conditions to filter results.
4. ORDER BY sorts results in ascending (ASC) or descending (DESC) order.
5. LIMIT restricts the number of rows returned in a query

Assume we have an employees table with the following data:

Table: employees
I name department salary
d
1 Alice HR 60000
2 Bob IT 45000
3 Charlie Finance 55000
4 David IT 70000
5 Emma HR 48000

1. Retrieving Specific Columns

Query:

SELECT name, salary FROM employees WHERE salary > 50000;

Explanation:

 This query selects only the name and salary columns.
 It filters employees earning more than 50,000.