Assignment: Introduction to SQL Server | SQL for Beginners - Software Development PDF Download

Multiple Choice Questions (MCQs)

Q1: What is SQL Server?
(a) A programming language
(b) A database management system
(c) An operating system
(d) A cloud storage service
Ans: (b)

Q2: Which language is used to interact with SQL Server?
(a) Python
(b) Java
(c) Structured Query Language (SQL)
(d) HTML
Ans: (c)

Q3: What is the primary function of SQL Server Management Studio (SSMS)?
(a) To edit images
(b) To manage and interact with SQL Server databases
(c) To create web pages
(d) To develop mobile applications
Ans: (b)

Q4: Which of the following is NOT a type of SQL command?
(a) Data Manipulation Language (DML)
(b) Data Definition Language (DDL)
(c) Hypertext Markup Language (HTML)
(d) Data Control Language (DCL)
Ans: (c)

Q5: What is the purpose of the SELECT statement in SQL?
(a) To delete records
(b) To retrieve data from a database
(c) To create a new database
(d) To modify existing data
Ans: (b)

Q6: What does the WHERE clause do in an SQL query?
(a) Sorts data
(b) Filters records based on conditions
(c) Deletes a database
(d) Creates a table
Ans: (b)

Q7: Which of the following is an example of DML (Data Manipulation Language)?
(a) CREATE
(b) ALTER
(c) UPDATE
(d) DROP
Ans: (c)

Q8: Which function is used to return the total number of rows in an SQL table?
(a) COUNT()
(b) SUM()
(c) AVG()
(d) MAX()
Ans: (a)

Higher Order Thinking Skills (HOTS)

Q1: Write an SQL query to find duplicate records in a table named Employees based on the Email column.
Ans:
SELECT Email, COUNT(*) 
FROM Employees 
GROUP BY Email 
HAVING COUNT(*) > 1;

Q2: How would you optimize an SQL query that retrieves data from a large table with millions of rows? Provide an example.
Ans: Indexing and query optimization techniques can be used. Example:
CREATE INDEX idx_employee_name ON Employees(Name);
SELECT * FROM Employees WHERE Name = 'John Doe';

Q3: Write an SQL query to fetch the second highest salary from a table named Salaries.
Ans:
SELECT MAX(Salary) 
FROM Salaries 
WHERE Salary < (SELECT MAX(Salary) FROM Salaries);

Q4: How can you ensure that an UPDATE query only modifies records that meet a specific condition? Provide an example.
Ans: Use the WHERE clause to filter records. Example:
UPDATE Employees 
SET Salary = Salary * 1.10 
WHERE Department = 'IT';

Q5: Write an SQL query to delete duplicate records from a table while keeping one instance of each.
Ans:
DELETE FROM Employees 
WHERE ID NOT IN (
SELECT MIN(ID) 
FROM Employees 
GROUP BY Email
);

Fill in the Blanks

Q1: SQL Server is developed by Microsoft as a __________ database management system.
Ans: relational

Q2: DDL (__________) is used to define and modify database structures.
Ans: Data Definition Language

Q3: The __________ uniquely identifies each record in a table.
Ans: Primary Key

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

Q5: __________ helps eliminate data redundancy and improves database efficiency.
Ans: Normalization

True or False

1. SQL Server is an open-source database management system.
Ans: False

2. The INSERT statement is used to add new records to a database.
Ans: True

3. A primary key can contain duplicate values in a table.
Ans: False

4. The HAVING clause is used with aggregate functions to filter records.
Ans: True

5. SQL supports both relational and non-relational database structures.
Ans: False

Hands-On Questions

Q1: Write an SQL query to create a table named Students with columns ID, Name, and Age.
Ans:
CREATE TABLE Students (
    ID INT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT
);

Q2: Write an SQL query to insert three records into the Students table.
Ans:
INSERT INTO Students (ID, Name, Age) VALUES
(1, 'Alice', 20),
(2, 'Bob', 22),
(3, 'Charlie', 18);

Q3: Write an SQL query to retrieve all records from the Students table where the age is greater than 18.
Ans:
SELECT * FROM Students WHERE Age > 18;

Q4: Write an SQL query to update the Name column of a student with ID 2.
Ans:
UPDATE Students SET Name = 'Robert' WHERE ID = 2;

Q5: Write an SQL query to delete all students whose age is less than 16.
Ans:
DELETE FROM Students WHERE Age < 16;