Assignment: SQL Queries and Data Manipulation | SQL for Beginners - Software Development PDF Download

Multiple Choice Questions (MCQs)

Q1: Which SQL statement is used to update existing records in a table?
(a) MODIFY
(b) UPDATE
(c) CHANGE
(d) ALTER
Ans: (b) UPDATE

Q2: How do you delete all records from a table without deleting the table itself?
(a) DELETE * FROM table_name
(b) DROP table_name
(c) TRUNCATE TABLE table_name
(d) REMOVE table_name
Ans: (c) TRUNCATE TABLE table_name

Q3: Which SQL clause is used to filter results based on a condition?
(a) WHERE
(b) ORDER BY
(c) GROUP BY
(d) FILTER
Ans: (a) WHERE

Q4: What does the JOIN clause do in SQL?
(a) Merges two tables based on a related column
(b) Deletes duplicate records
(c) Creates a new table
(d) Sorts data in ascending order
Ans: (a) Merges two tables based on a related column

Q5: What is the difference between HAVING and WHERE clauses?
(a) WHERE filters rows before aggregation, while HAVING filters groups after aggregation
(b) They are interchangeable
(c) HAVING is used for updating records, and WHERE is used for selecting records
(d) HAVING filters individual rows, while WHERE filters columns
Ans: (a) WHERE filters rows before aggregation, while HAVING filters groups after aggregation

Q6: What is the function of the DISTINCT keyword in SQL?
(a) Removes duplicate values from a result set
(b) Counts the number of rows in a table
(c) Sorts data in ascending order
(d) Deletes specific rows from a table
Ans: (a) Removes duplicate values from a result set

Q7: Which SQL function is used to find the highest value in a column?
(a) MIN()
(b) MAX()
(c) SUM()
(d) COUNT()
Ans: (b) MAX()

Q8: What does the LIMIT clause do in an SQL query?
(a) Restricts the number of rows returned by a query
(b) Groups similar rows together
(c) Sorts query results
(d) Deletes duplicate records
Ans: (a) Restricts the number of rows returned by a query

Higher Order Thinking Skills (HOTS)

Q1: Write an SQL query to create an index on the Salary column of the Employees table to improve query performance.
Ans:
CREATE INDEX idx_salary ON Employees(Salary);

Q2: Write an SQL query to enforce the UNIQUE constraint on the Email column in an Employees table.
Ans:
ALTER TABLE Employees ADD CONSTRAINT unique_email UNIQUE(Email);

Q3: How can views improve database security? Write an SQL query to create a view that only displays Employee names and their department.
Ans:
CREATE VIEW EmployeeView AS
SELECT Name, Department FROM Employees;

Q4: Write an SQL query to create a stored procedure that increases the salary of all employees in a specific department by 10%.
Ans:
CREATE PROCEDURE IncreaseSalary @Department VARCHAR(50)
AS
BEGIN
    UPDATE Employees
    SET Salary = Salary * 1.10
    WHERE Department = @Department;
END;

Q5: Write an SQL query to normalize a table by splitting Employee details into two tables: Employees (ID, Name, DepartmentID) and Departments (DepartmentID, DepartmentName).
Ans:
CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(100)
);
CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    Name VARCHAR(100),
    DepartmentID INT,
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

Fill in the Blanks

Q1: The __________ clause is used to sort query results in ascending or descending order.
Ans: ORDER BY

Q2: The __________ statement removes specific records from a table, while TRUNCATE removes all records without logging individual row deletions.
Ans: DELETE

Q3: The __________ operator combines the results of two or more SELECT statements, removing duplicate rows by default.
Ans: UNION

Q4: The __________ clause is used to filter aggregated results after the GROUP BY clause has been applied.
Ans: HAVING

Q5: The __________ clause returns only the matching records from two tables based on a specified condition.
Ans: INNER JOIN

True or False

1. The GROUP BY clause is used to group rows that have the same values in specified columns.
Ans: True

2. The ALTER statement is used to delete all records from a table.
Ans: False

3. A foreign key in SQL allows NULL values by default.
Ans: True

4. The LEFT JOIN retrieves only matching records from both tables.
Ans: False

5. The HAVING clause can be used without GROUP BY in some cases.
Ans: True

Hands-On Questions

Q1: Write an SQL query to retrieve all records from a table named Employees where the salary is greater than 50,000.
Ans: SELECT * FROM Employees WHERE Salary > 50000;

Q2: Write an SQL query to update the department of an employee with ID 10 to 'HR'.
Ans: UPDATE Employees SET Department = 'HR' WHERE ID = 10;

Q3: Write an SQL query to delete all employees whose contract has expired before '2024-01-01'.
Ans: DELETE FROM Employees WHERE Contract_End_Date < '2024-01-01';

Q4: Write an SQL query to retrieve the names of employees whose salaries fall within the range of 40,000 to 70,000.
Ans: SELECT Name FROM Employees WHERE Salary BETWEEN 40000 AND 70000;

Q5: Write an SQL query to count the number of employees in each department using GROUP BY.
Ans: SELECT Department, COUNT(*) AS Employee_Count FROM Employees GROUP BY Department;