1.

Write SQL queries for DDL commands: Create,

Desc, Alter, Rename, Drop.

1. CREATE: To create a new table

CREATE TABLE employees (
employee_id INT PRIMARY KEY,
rst_name VARCHAR(50),
last_name VARCHAR(50),
hire_date DATE,
salary DECIMAL(10, 2)
);

2. DESC (or DESCRIBE): To describe the structure of a table

DESCRIBE employees;

3. ALTER: To modify the structure of an existing table.

• Add a Column:
ALTER TABLE employees
ADD COLUMN department VARCHAR(100);

• Modify a Column:
ALTER TABLE employees
MODIFY COLUMN salary DECIMAL(12, 2);

• Drop a Column:
ALTER TABLE employees
DROP COLUMN department;
• Rename a Table:
ALTER TABLE employees
RENAME TO staff;
• Rename a Column:
ALTER TABLE employees
RENAME COLUMN rst_name TO rst_name_new;
4. RENAME: To rename an existing table
RENAME TABLE employees TO staff;

5. DROP: To delete a table, view, or database.

• Drop a Table:
DROP TABLE employees;
• Drop a Database:
DROP DATABASE company_db;
• Drop a View:
DROP VIEW employee_view;

2. Write SQL queries by using DML

commands: Insert, Select, Update, Delete.

1. INSERT: To add data into a table.

• Insert a Single Row:
INSERT INTO employees (employee_id, rst_name,
last_name, hire_date, salary)
VALUES (1, 'John', 'Doe', '2023-01-15', 55000.00);

• Insert Multiple Rows:

INSERT INTO employees (employee_id, rst_name,
last_name, hire_date, salary)
VALUES
(2, 'Jane', 'Smith', '2022-05-20', 60000.00),
(3, 'Mark', 'Johnson', '2021-03-10', 47000.00),
(4, 'Emily', 'Davis', '2024-08-01', 75000.00);
2. SELECT: To retrieve data from a table.
• Select All Columns:
SELECT * FROM employees;
• Select Specic Columns:
SELECT rst_name, last_name, salary FROM employees;

• Select with Condition:

SELECT * FROM employees
WHERE salary > 50000;

• Select with Sorting (Ascending/Descending):

SELECT * FROM employees
ORDER BY hire_date DESC;

• Select with LIMIT (limit number of results):

SELECT * FROM employees
LIMIT 5;
• Select with LIMIT (limit number of results):
SELECT * FROM employees
LIMIT 5;

3. UPDATE: To modify existing data in a table.

• Update a Single Row:
UPDATE employees
SET salary = 60000
WHERE employee_id = 1;
• Update Multiple Rows:
UPDATE employees
SET salary = salary + 5000
WHERE hire_date < '2023-01-01';
• Update Multiple Columns:
UPDATE employees
SET rst_name = 'Alice', last_name = 'Williams', salary = 65000
WHERE employee_id = 2;
4. DELETE: To remove data from a table.
• Delete a Single Row:
DELETE FROM employees
WHERE employee_id = 3;
• Delete Multiple Rows:
DELETE FROM employees
WHERE salary < 50000;
• Delete All Rows in a Table (without dropping the table):
DELETE FROM employees;

3. Write SQL queries using Logical operations:

AND, OR, NOT, IN, BETWEEN, LIKE.

1. AND
Used to combine multiple conditions that must all be true.
SELECT * FROM employees
WHERE salary > 50000 AND hire_date > '2023-01-01';
2. OR
Used to combine conditions where at least one must be true.
SELECT * FROM employees
WHERE rst_name = 'John' OR last_name = 'Smith';
3. NOT
Negates a condition (i.e. selects rows where the condition is false).
SELECT * FROM employees
WHERE NOT salary > 60000;
OR
SELECT * FROM employees
WHERE NOT (rst_name = 'John' AND last_name = 'Doe');
4. IN
Checks if a value matches any value in a list.
SELECT * FROM employees
WHERE department IN ('HR', 'Finance', 'Engineering');
You can also use NOT IN:
SELECT * FROM employees
WHERE department NOT IN ('Sales', 'Marketing');

5. BETWEEN
Checks if a value is within a range (inclusive).
SELECT * FROM employees
WHERE salary BETWEEN 50000 AND 70000;
Or with dates:
SELECT * FROM employees
WHERE hire_date BETWEEN '2022-01-01' AND '2023-12-31';

6. LIKE
Used for pattern matching in text elds.
//Names that start with 'J'
SELECT * FROM employees
WHERE rst_name LIKE 'J%';

//Names that end with 'son'

SELECT * FROM employees
WHERE last_name LIKE '%son';

//Names that contain 'ar'

SELECT * FROM employees
WHERE rst_name LIKE '%ar%';

//Underscore (_) matches a single character

SELECT * FROM employees
WHERE rst_name LIKE '_ohn';
//Matches "John", "Bohn", etc.
4. Write SQL queries using Arithmetic operators:
+, -, *, /, %.

1. Addition (+)
Adds 5000 to each employee’s salary.
SELECT rst_name, salary, salary + 5000 AS increased_salary
FROM employees;
2. Subtraction (-)
Subtracts 2000 from each salary.
SELECT rst_name, salary, salary - 2000 AS adjusted_salary
FROM employees;

3. Multiplication (*)
Increases salary by 10% (useful for calculating bonuses or tax).
SELECT rst_name, salary, salary * 1.10 AS salary_with_bonus
FROM employees;

4. Division (/)
Converts annual salary to monthly salary.
SELECT rst_name, salary, salary / 12 AS monthly_salary
FROM employees;

5. Modulus (%)
Checks if salary is even or odd (e.g., 0 = even, 1 = odd).
SELECT employee_id, salary % 2 AS remainder
FROM employees;

Combined Example:

SELECT rst_name, salary,

(salary + 1000) * 1.05 AS projected_salary,
salary % 1000 AS salary_mod_1000
FROM employees
WHERE salary > 40000;
5. Write SQL demonstrating the use of
Comparison operators: =,<>,>,>=,<,<=.

1. Equals (=)
Checks if two values are equal.
SELECT * FROM employees
WHERE department = 'Finance';
2. Not Equals (<>)
Checks if two values are not equal.
SELECT * FROM employees
WHERE salary <> 50000;
3. Greater Than (>)
Checks if a value is greater than another.
SELECT * FROM employees
WHERE salary > 60000;
4. Greater Than or Equal To (>=)
Retrieves employees with salary 70,000 or more.
SELECT * FROM employees
WHERE salary >= 70000;
5. Less Than (<)
Checks if a value is less than another.
SELECT * FROM employees
WHERE salary < 45000;

6. Less Than or Equal To (<=)

Retrieves employees hired on or before December 31, 2023.
SELECT * FROM employees
WHERE hire_date <= '2023-12-31';
Combined Example:
Retrieves HR employees earning 50,000+ and hired before 2023.
SELECT * FROM employees
WHERE department = 'HR'
AND salary >= 50000
AND hire_date < '2023-01-01';
6. Write SQL queries using Aggregate functions:
AVG, MIN, MAX, SUM, COUNT.

1. AVG() — Average Value

Returns the average salary of all employees.
SELECT AVG(salary) AS average_salary
FROM employees;
2. MIN() — Minimum Value
Returns the lowest salary in the table.
SELECT MIN(salary) AS lowest_salary
FROM employees;
3. MAX() — Maximum Value
Returns the highest salary in the table.
SELECT MAX(salary) AS highest_salary
FROM employees;
4. SUM() — Total Value
Returns the total sum of all salaries.
SELECT SUM(salary) AS total_salary_paid
FROM employees;
5. COUNT() — Number of Rows
Returns the total number of employees.
SELECT COUNT(*) AS total_employees
FROM employees;
You can also count specic columns:
SELECT COUNT(department) AS departments_count
FROM employees;
Grouped Example — Aggregate functions with GROUP BY
SELECT department,
COUNT(*) AS employee_count,
AVG(salary) AS avg_salary,
MIN(salary) AS min_salary,
MAX(salary) AS max_salary,
SUM(salary) AS total_salary
FROM employees
GROUP BY department;
7. Write SQL queries to get current date and time:
NOW, CURDATE, CURTIME, LAST_DAY,
DATE_FORMAT.
1. NOW()
Returns the current date and time (YYYY-MM-DD HH:MM:SS).
SELECT NOW() AS current_datetime;
2. CURDATE()
Returns the current date only (YYYY-MM-DD).
SELECT CURDATE() AS current_date;
3. CURTIME()
Returns the current time only (HH:MM:SS).
SELECT CURTIME() AS current_time;
4. LAST_DAY(date)
Returns the last day of the month for a given date.
SELECT LAST_DAY(CURDATE()) AS last_day_of_month;
5. DATE_FORMAT(date, format)
Used to format dates/times in various ways.
SELECT DATE_FORMAT(CURDATE(), '%d-%w-%m-%Y')
AS formatted_date;

8. Write SQL queries using Character functions:

ASCII, CHAR, CONCAT, INSTR, INSERT,
LENGTH, LOWER, UPPER.
1. ASCII(string)
Returns the ASCII code of the rst character in the string.
SELECT ASCII('A') AS ascii_value; -- Output: 65
2. CHAR(number)
Returns the character for the given ASCII code.
SELECT CHAR(66) AS character_value; -- Output: 'B'
3. CONCAT(string1, string2, ...)
Combines multiple strings into one.
SELECT CONCAT(rst_name, ' ', last_name) AS full_name
FROM employees;
4. INSTR(string, substring)
Returns the position of the rst occurrence of a substring.
SELECT INSTR('Database', 'base') AS position_found;
5. INSERT(string, start, length, new_substring)
Replaces part of a string with a new substring.
SELECT INSERT('Database', 5, 3, '___') AS modied_string;
6. LENGTH(string)
Returns the length in bytes of the string.
SELECT LENGTH('Database') AS string_length;
7. LOWER(string)
Converts all characters to lowercase.
SELECT LOWER('HeLLo WorLD') AS lowercase_text;
8. UPPER(string)
Converts all characters to uppercase.
SELECT UPPER('HeLLo WorLD') AS uppercase_text;
Combined Example:
SELECT
rst_name,
last_name,
CONCAT(UPPER(rst_name), ' ',
LOWER(last_name)) AS formatted_name,
LENGTH(rst_name) AS rst_name_length,
ASCII(SUBSTRING(rst_name, 1, 1)) AS ascii_of_rst_letter,
INSTR(last_name, 's') AS position_of_s_in_lastname
FROM employees;
9. Write SQL queries using Number functions:
POWER, ROUND, SQRT, EXP, GREATEST,
LEAST, MOD, FLOOR, CEIL.
1. POWER(x, y)
Raises x to the power of y (i.e.,xy).
SELECT POWER(2, 3) AS result; //Output: 8
2. ROUND(number, decimal_places)
Rounds a number to a specied number of decimal places.
SELECT ROUND(123.4567, 2) AS rounded_value;
//Output: 123.46
3. SQRT(number)
Returns the square root.
SELECT SQRT(49) AS square_root; //Output: 7
4. EXP(number)
Returns the exponential value of the number
SELECT EXP(1) AS e_to_power_1; //Output: ~2.71828
5. GREATEST(value1, value2, ...)
Returns the largest value from the list.
SELECT GREATEST(100, 200, 150) AS max_value; //Output: 200
6. LEAST(value1, value2, ...)
Returns the smallest value from the list.
SELECT LEAST(100, 200, 150) AS min_value; //Output: 100
7. MOD(x, y) or x % y
Returns the remainder when x is divided by y.
SELECT MOD(17, 5) AS remainder; //Output: 2
7. MOD(x, y) or x % y
Returns the remainder when x is divided by y.
SELECT MOD(17, 5) AS remainder; //Output: 2
Or use %:
SELECT 17 % 5 AS remainder; //Output: 2
8. FLOOR(number)
Rounds down to the nearest whole number.
SELECT FLOOR(8.9) AS oored_value; //Output: 8

9. CEIL(number) or CEILING(number)
Rounds up to the nearest whole number.
SELECT CEIL(8.1) AS ceiled_value; //Output: 9
10.Write SQL queries for Relational algebra
operations: SELECT, PROJECT, UNION,
INTERSECTION. DIFFERENCE
1. SELECT (σ — Selection)
Relational algebra: Select rows that satisfy a condition.
//Get employees from 'HR' department
SELECT *
FROM employees
WHERE department = 'HR';
2. PROJECT (π — Projection)
Relational algebra: Select specic columns (attributes only).
//Get only rst names and departments of employees
SELECT rst_name, department
FROM employees;
3. UNION (∪)
Relational algebra: Combine rows from two tables (no duplicates).
//Get a list of all employee and manager names (unique only)
SELECT rst_name, last_name FROM employees
UNION
SELECT rst_name, last_name FROM managers;
4. INTERSECTION (∩)
Relational algebra: Return rows that are common to both tables.
//Get employees who are also managers
SELECT rst_name, last_name
FROM employees
INTERSECT
SELECT rst_name, last_name
FROM managers;
5. DIFFERENCE (−)
Relational algebra: Return rows in one table but not in another.
//Get employees who are not managers
SELECT rst_name, last_name
FROM employees
EXCEPT
SELECT rst_name, last_name
FROM managers;