UNIT – IV: Structured Query Language (SQL)

 

1. Concept of SQL

• SQL is a standard language for managing relational databases.

• Categories: DDL, DML, DCL, TCL.

---

2. Data Types in SQL

• Integer, Float, Double → Numeric values

• Char(n), Varchar(n) → Strings

• Date, Time, Timestamp → Date & time values

• Number(p,s) → Numeric with precision

---

3. DDL Commands

• CREATE: Creates table.

CREATE TABLE Student(RollNo INT, Name VARCHAR(50));

• ALTER: Modifies table structure.

• DROP: Deletes table.

• RENAME: Changes table name.

• TRUNCATE: Deletes all rows, keeps structure.

• COMMENT: Adds comments.

---

4. DML Commands

• SELECT: Retrieve data.

• INSERT: Add rows.

• UPDATE: Modify rows.

• DELETE: Remove rows.

• MERGE: Insert/update simultaneously.

• CALL: Call stored procedure.

• EXPLAIN PLAN: Shows execution plan.

• LOCK TABLE: Prevents changes by others.

---

5. DCL Commands

• GRANT: Give access.

• REVOKE: Remove access.

---

6. TCL Commands

• COMMIT: Save changes.

• ROLLBACK: Undo changes.

• SAVEPOINT: Create checkpoint.

• SET TRANSACTION: Modify transaction properties.

---

7. WHERE Clause with Operators

• Comparison (=, >, <, !=)

• Logical (AND, OR, NOT)

• LIKE (pattern matching)

• IN, BETWEEN

---

8. Constraints

• Primary Key

• Foreign Key

• Unique

• Check

• Not Null

---

9. SQL Functions

• Aggregate: COUNT, SUM, AVG, MAX, MIN.

• Scalar: UPPER, LOWER, LENGTH, SUBSTR, ROUND.

---

10. SQL Joins

• INNER JOIN: Common records.

• LEFT JOIN: All left + matching right.

• RIGHT JOIN: All right + matching left.

• FULL OUTER JOIN: All records from both.

---

Unit–IV Long Questions with Answers

1. Explain SQL and its uses in DBMS.

2. Explain SQL data types with examples.

3. Explain CREATE, ALTER, DROP with examples.

4. Differentiate DELETE and TRUNCATE.

5. Explain SELECT with WHERE clause.

6. Explain INSERT with example.

7. Explain UPDATE with example.

8. Explain DELETE with example.

9. Explain MERGE with example.

10. Explain GRANT and REVOKE with syntax.

11. Explain COMMIT, ROLLBACK, SAVEPOINT.

12. Explain constraints with examples.

13. Explain aggregate functions with SQL queries.

14. Explain scalar functions with SQL queries.

15. Explain SQL joins with examples.

Q1. Define SQL and explain its categories.

Answer:
SQL is a structured query language used to interact with relational databases.
Categories:

• DDL – Create structure

• DML – Manipulate data

• DCL – Control permissions

• TCL – Manage transactions

---

Q2. Explain SQL data types with examples.

Answer:

• Integer: INT, SMALLINT

• Float: REAL, FLOAT

• Char: CHAR(10), VARCHAR(20)

• Date/Time: DATE, TIME, TIMESTAMP
  Example:

CREATE TABLE Student(RollNo INT, Name VARCHAR(50), DOB DATE);

---

Q3. Explain CREATE, ALTER, DROP with examples.

Answer:

• CREATE: CREATE TABLE Student(RollNo INT, Name VARCHAR(20));

• ALTER: ALTER TABLE Student ADD Age INT;

• DROP: DROP TABLE Student;

---

Q4. Differentiate DELETE and TRUNCATE with example.

Answer:

• DELETE: Removes rows, can use WHERE. Rollback possible.

• TRUNCATE: Removes all rows, faster, cannot rollback.

---

Q5. Explain SELECT with WHERE clause.

Answer:

SELECT * FROM Student WHERE Age > 20;
SELECT Name FROM Student WHERE City='Surat';

---

Q6. Explain INSERT with example.

Answer:

INSERT INTO Student VALUES(1, 'Amit', 'Surat');
INSERT INTO Student(Name, City) VALUES('Ravi', 'Baroda');

---

Q7. Explain UPDATE with example.

Answer:

UPDATE Student SET City='Mumbai' WHERE RollNo=1;

---

Q8. Explain DELETE with example.

Answer:

DELETE FROM Student WHERE City='Surat';

---

Q9. Explain MERGE with example.

Answer:

MERGE INTO Student S
USING Temp T
ON (S.RollNo = T.RollNo)
WHEN MATCHED THEN UPDATE SET S.City = T.City
WHEN NOT MATCHED THEN INSERT VALUES(T.RollNo, T.Name, T.City);

---

Q10. Explain GRANT and REVOKE.

Answer:

• GRANT: Gives permission

GRANT SELECT, UPDATE ON Student TO user1;

• REVOKE: Removes permission

REVOKE UPDATE ON Student FROM user1;

---

Q11. Explain COMMIT, ROLLBACK, SAVEPOINT.

Answer:

• COMMIT: Saves permanently.

• ROLLBACK: Undo changes.

• SAVEPOINT: Marks intermediate point.

---

Q12. Explain constraints in SQL.

Answer:

• Primary Key

• Foreign Key

• Unique

• Not Null

• Check
  Example:

CREATE TABLE Emp(ID INT PRIMARY KEY, Salary INT CHECK(Salary>0));

---

Q13. Explain aggregate functions with examples.

Answer:

SELECT COUNT(*), AVG(Salary), MAX(Salary) FROM Employee;

---

Q14. Explain scalar functions with examples.

Answer:

SELECT UPPER(Name), LENGTH(Name) FROM Student;

---

Q15. Explain SQL joins with examples.

Answer:

• INNER JOIN: Only matching rows

• LEFT JOIN: All left + matching right

• RIGHT JOIN: All right + matching left

• FULL JOIN: All records

SELECT S.Name, C.Course FROM Student S 
INNER JOIN Course C ON S.RollNo=C.RollNo;