  TRUNCATE: Removes all rows, but retains the table

structure.
 DROP: Removes the entire table, including its structure.
 DELETE: Removes specific rows, preserving the table
structure and other rows.
 ere are some practice questions based on the SQL concepts you've reviewed:

Database and Table Management

1. Create a New Database:

o Write an SQL query to create a database named SchoolDB.

sql
Copy code
CREATE DATABASE SchoolDB;

2. Use a Database:
o Write an SQL query to select the SchoolDB database for subsequent operations.

sql
Copy code
USE SchoolDB;

3. Delete a Database:
o Write an SQL query to delete the SchoolDB database.

sql
Copy code
DROP DATABASE SchoolDB;

4. Create a Table:
o Write an SQL query to create a table named Teachers with the following
columns: TeacherID (integer), FirstName (nvarchar(50)), LastName
(nvarchar(50)), HireDate (date), and Subject (nvarchar(100)).

sql
Copy code
CREATE TABLE Teachers (
TeacherID int,
FirstName nvarchar(50),
LastName nvarchar(50),
HireDate date,
Subject nvarchar(100)
);
 5. Drop a Table:
o Write an SQL query to delete the Teachers table from the database.

sql
Copy code
DROP TABLE Teachers;

6. Truncate a Table:
o Write an SQL query to truncate the Students table, removing all its data but
keeping the table structure.

sql
Copy code
TRUNCATE TABLE Students;

SQL Constraints

7. Add a NOT NULL Constraint:

o Write an SQL query to create a Courses table with the following columns:
CourseID (integer, primary key), CourseName (nvarchar(100), NOT NULL), and
Credits (int, NOT NULL).

sql
Copy code
CREATE TABLE Courses (
CourseID int PRIMARY KEY,
CourseName nvarchar(100) NOT NULL,
Credits int NOT NULL
);

8. Add a UNIQUE Constraint:

o Write an SQL query to add a UNIQUE constraint to the Email column of the
Teachers table.

sql
Copy code
ALTER TABLE Teachers
ADD CONSTRAINT UNIQUE_Teachers_Email UNIQUE (Email);

9. Add a Primary Key Constraint:

o Write an SQL query to create a Departments table with DepartmentID (int,
primary key) and DepartmentName (nvarchar(100), NOT NULL).

sql
Copy code
CREATE TABLE Departments (
DepartmentID int PRIMARY KEY,
DepartmentName nvarchar(100) NOT NULL
);
 10. Add a Foreign Key Constraint:
o Write an SQL query to create a Enrollments table with StudentID (int, foreign
key referencing Students(StudentID)), CourseID (int, foreign key referencing
Courses(CourseID)), and EnrollmentDate (date).

sql
Copy code
CREATE TABLE Enrollments (
StudentID int,
CourseID int,
EnrollmentDate date,
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

11. Add a Check Constraint:

o Write an SQL query to add a check constraint on the Credits column of the
Courses table to ensure that the credits are between 1 and 5.

sql
Copy code
ALTER TABLE Courses
ADD CONSTRAINT CHECK_Credits CHECK (Credits >= 1 AND Credits <= 5);

12. Add a Default Constraint:

o Write an SQL query to set a default value of 3 credits for the Credits column in
the Courses table.

sql
Copy code
ALTER TABLE Courses
ADD CONSTRAINT DEFAULT_Credits DEFAULT 3 FOR Credits;

Data Modification Language (DML)

13. Insert Data into a Table:

o Write an SQL query to insert a new row into the Students table with StudentID
= 1, FirstName = 'John', LastName = 'Doe', and DateOfBirth = '2000-
01-01'.

sql
Copy code
INSERT INTO Students (StudentID, FirstName, LastName, DateOfBirth)
VALUES (1, 'John', 'Doe', '2000-01-01');

14. Update Data in a Table:

o Write an SQL query to update the LastName of the student with StudentID = 1
to 'Smith'.

sql
 Copy code
UPDATE Students
SET LastName = 'Smith'
WHERE StudentID = 1;

15. Delete Data from a Table:

o Write an SQL query to delete all students whose LastName is 'Doe' from the
Students table.

sql
Copy code
DELETE FROM Students
WHERE LastName = 'Doe';

16. Delete All Data from a Table:

o Write an SQL query to remove all data from the Enrollments table without
deleting the table structure.

sql
Copy code
TRUNCATE TABLE Enrollments;

Basic SELECT Statements

1. Retrieve All Columns and Rows:

o Write an SQL query to retrieve all columns and rows from a table named
Employees.

sql
Copy code
SELECT *
FROM Employees;

2. Retrieve Specific Columns:

o Write an SQL query to retrieve only the FirstName, LastName, and Email
columns from the Customers table.

sql
Copy code
SELECT FirstName, LastName, Email
FROM Customers;

3. Filtering Rows Using WHERE Clause:

o Write an SQL query to retrieve all rows from the Orders table where the
OrderDate is after January 1, 2023.

sql
Copy code
SELECT *
FROM Orders
 WHERE OrderDate > '2023-01-01';

LIKE Operator

4. Use the LIKE Operator:

o Write an SQL query to find all employees whose LastName starts with 'S' in the
Employees table.

sql
Copy code
SELECT *
FROM Employees
WHERE LastName LIKE 'S%';

5. LIKE with Wildcards:

o Write an SQL query to find all courses in the Courses table where the course
name contains 'Math' anywhere in the name.

sql
Copy code
SELECT *
FROM Courses
WHERE CourseName LIKE '%Math%';

Renaming Columns with AS Keyword

6. Renaming Columns:
o Write an SQL query to retrieve the ProductName and Price columns from the
Products table, but rename them as Product and Cost, respectively.

sql
Copy code
SELECT ProductName AS Product, Price AS Cost
FROM Products;

Using Built-in Functions

7. Use Built-in Functions:

o Write an SQL query to get the current date and time from the database server.

sql
Copy code
SELECT GETDATE();

8. Extracting Substrings:
o Write an SQL query to retrieve the first 3 characters of the FirstName column
from the Employees table.

sql
 Copy code
SELECT SUBSTRING(FirstName, 1, 3) AS ShortName
FROM Employees;

ORDER BY Clause

9. Sorting Data:
o Write an SQL query to retrieve the ProductName and Price from the Products
table, sorted by Price in descending order.

sql
Copy code
SELECT ProductName, Price
FROM Products
ORDER BY Price DESC;

TOP Clause

10. Using TOP:

o Write an SQL query to retrieve the top 5 highest-priced products from the
Products table.

sql
Copy code
SELECT TOP 5 ProductName, Price
FROM Products
ORDER BY Price DESC;

This is a well-organized explanation of different types of database keys,

clearly outlining their definitions, examples, and key points. Here's a brief
summary:
 Super Key: A set of one or more attributes that can uniquely identify a
record. All candidate keys are super keys, but not all super keys are
candidate keys.
 Candidate Key: A minimal super key that uniquely identifies a record. It
cannot be NULL, must be unique, and can be a composite key.
 Primary Key: A chosen candidate key to uniquely identify records in a table.
It must be unique and not NULL, with only one primary key allowed per table.
 Composite Key: A primary key made up of two or more attributes, where
none can independently identify a record.
 Secondary or Alternative Key: Candidate keys not chosen as the primary
key. They are unique but not the main identifier.
 Non-key Attributes: Attributes that are not part of any candidate key, like
name and age, which don't help in identifying records.
 Non-prime Attributes: Attributes not part of the primary key, which may
include secondary key attributes.
 Domain Constraints
 Domain constraints define the permissible values that a
column can take. They ensure that data adheres to
specific rules related to its type, format, or range.
Domain constraints can be enforced through data types,
check constraints, and default values.

Key Constraints
Key constraints ensure that the data within a table is uniquely
identifiable and maintain the integrity of relationships between
tables.

Referential integrity constraints ensure that

relationships between tables are maintained correctly.
These constraints ensure that foreign keys in one table
correspond to primary keys in another table,
maintaining consistency between related tables.

Certainly! Here are more practice questions on SQL concepts, including solved examples for
each:

1. Database and Table Management

Create a Database with Specific Encoding:

Question: Write an SQL query to create a database named LibraryDB with UTF-8 encoding.

Solution:

sql
Copy code
CREATE DATABASE LibraryDB
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Select and List Tables in a Database:

Question: Write an SQL query to list all tables in the LibraryDB database.

Solution:

sql
Copy code
USE LibraryDB;

SHOW TABLES;

Create a Table with Auto-Incrementing Primary Key:

Question: Write an SQL query to create a table named Books with the following columns:
BookID (auto-incrementing integer, primary key), Title (nvarchar(200)), Author
(nvarchar(100)), and PublicationYear (int).

Solution:

sql
Copy code
CREATE TABLE Books (
BookID int AUTO_INCREMENT PRIMARY KEY,
Title nvarchar(200),
Author nvarchar(100),
PublicationYear int
);

Drop Multiple Tables:

Question: Write an SQL query to drop the Books and Authors tables from the database.

Solution:

sql
Copy code
DROP TABLE IF EXISTS Books;
DROP TABLE IF EXISTS Authors;

Truncate a Table with Conditional Check:

Question: Write an SQL query to truncate the LibraryRecords table but only if the table
contains fewer than 1000 rows.

Solution:
sql
Copy code
SET @row_count = (SELECT COUNT(*) FROM LibraryRecords);

IF @row_count < 1000 THEN

TRUNCATE TABLE LibraryRecords;
END IF;

(Note: The conditional truncation might require a stored procedure or script in some SQL
implementations.)

2. SQL Constraints

Add a Default Constraint with Different Data Type:

Question: Write an SQL query to set a default value of 0 for the Discount column in the
Products table, assuming Discount is of type decimal(5,2).

Solution:

sql
Copy code
ALTER TABLE Products
ADD CONSTRAINT DF_Discount DEFAULT 0 FOR Discount;

Add a Composite Primary Key:

Question: Write an SQL query to create a table named OrderItems with columns OrderID (int),
ProductID (int), and Quantity (int), and set both OrderID and ProductID as a composite
primary key.

Solution:

sql
Copy code
CREATE TABLE OrderItems (
OrderID int,
ProductID int,
Quantity int,
PRIMARY KEY (OrderID, ProductID)
);

Add a Foreign Key with Cascade Delete:

Question: Write an SQL query to modify the OrderItems table to add a foreign key constraint
on OrderID that references Orders(OrderID) with cascade delete.

Solution:

sql
Copy code
ALTER TABLE OrderItems
ADD CONSTRAINT FK_OrderItems_Orders
FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
ON DELETE CASCADE;

Add a UNIQUE Constraint on Multiple Columns:

Question: Write an SQL query to add a UNIQUE constraint to ensure that the combination of
FirstName and LastName is unique in the Customers table.

Solution:

sql
Copy code
ALTER TABLE Customers
ADD CONSTRAINT UQ_Customers_Name UNIQUE (FirstName, LastName);

Add a CHECK Constraint for a String Column:

Question: Write an SQL query to add a CHECK constraint to ensure that the Status column in
the Orders table only contains values 'Pending', 'Shipped', or 'Delivered'.

Solution:

sql
Copy code
ALTER TABLE Orders
ADD CONSTRAINT CHK_Status CHECK (Status IN ('Pending', 'Shipped',
'Delivered'));

3. Data Modification Language (DML)

Insert Multiple Rows:

Question: Write an SQL query to insert three rows into the Books table with different titles and
authors.

Solution:

sql
Copy code
INSERT INTO Books (Title, Author, PublicationYear)
VALUES
('The Great Gatsby', 'F. Scott Fitzgerald', 1925),
('1984', 'George Orwell', 1949),
('To Kill a Mockingbird', 'Harper Lee', 1960);

Update Data in Multiple Rows:

Question: Write an SQL query to update the PublicationYear of all books where the Author
is 'J.K. Rowling' to 2024.

Solution:

sql
Copy code
UPDATE Books
SET PublicationYear = 2024
WHERE Author = 'J.K. Rowling';

Delete Specific Data:

Question: Write an SQL query to delete all rows from the Orders table where OrderDate is
before '2023-01-01'.

Solution:

sql
Copy code
DELETE FROM Orders
WHERE OrderDate < '2023-01-01';

Delete Data with JOIN Condition:

Question: Write an SQL query to delete all records from the OrderItems table where the
corresponding OrderID in the Orders table has a Status of 'Cancelled'.

Solution:

sql
Copy code
DELETE oi
FROM OrderItems oi
JOIN Orders o ON oi.OrderID = o.OrderID
WHERE o.Status = 'Cancelled';

4. Basic SELECT Statements

Retrieve Columns with Aggregation:

Question: Write an SQL query to retrieve the average price and total number of products from
the Products table.

Solution:

sql
Copy code
SELECT
 AVG(Price) AS AveragePrice,
COUNT(*) AS TotalProducts
FROM Products;

Retrieve Rows with Sorting and Limiting:

Question: Write an SQL query to retrieve the ProductName and Price from the Products table,
sorted by Price in ascending order, and limit the results to the top 10 rows.

Solution:

sql
Copy code
SELECT ProductName, Price
FROM Products
ORDER BY Price ASC
LIMIT 10;

Filtering Rows with Multiple Conditions:

Question: Write an SQL query to retrieve all rows from the Employees table where the
Department is 'Sales' and the Salary is greater than 50000.

Solution:

sql
Copy code
SELECT *
FROM Employees
WHERE Department = 'Sales' AND Salary > 50000;

5. LIKE Operator

Using LIKE with Multiple Wildcards:

Question: Write an SQL query to find all email addresses in the Customers table that end with
'example.com'.

Solution:

sql
Copy code
SELECT Email
FROM Customers
WHERE Email LIKE '%@example.com';

LIKE with Escape Character:

Question: Write an SQL query to find all product names in the Products table that contain an
underscore ('_') as part of the name, using escape character '' (backslash).

Solution:

sql
Copy code
SELECT ProductName
FROM Products
WHERE ProductName LIKE '%\_%' ESCAPE '\';

6. Renaming Columns with AS Keyword

Renaming Columns with Expressions:

Question: Write an SQL query to retrieve Price and its 10% discount from the Products table,
renaming the discount column as DiscountedPrice.

Solution:

sql
Copy code
SELECT
Price,
Price * 0.90 AS DiscountedPrice
FROM Products;

7. Using Built-in Functions

Extracting Parts of a Date:

Question: Write an SQL query to retrieve the year from the OrderDate column in the Orders
table.

Solution:

sql
Copy code
SELECT YEAR(OrderDate) AS OrderYear
FROM Orders;

Using String Functions:

Question: Write an SQL query to concatenate FirstName and LastName from the Employees
table with a space in between, and rename the concatenated result as FullName.

Solution:

sql
Copy code
SELECT
CONCAT(FirstName, ' ', LastName) AS FullName
FROM Employees;

8. ORDER BY Clause

Sorting with Multiple Columns:

Question: Write an SQL query to retrieve ProductName and Price from the Products table,
sorted by Price in ascending order and then by ProductName in descending order.

Solution:

sql
Copy code
SELECT ProductName, Price
FROM Products
ORDER BY Price ASC, ProductName DESC;

9. TOP Clause

Using TOP with a Percentage:

Question: Write an SQL query to retrieve the top 10% most expensive products from the
Products table.

Solution:

sql
Copy code
SELECT TOP (10) PERCENT ProductName, Price
FROM Products
ORDER BY Price DESC;

These solved questions cover a variety of SQL concepts, helping you practice and enhance your
skills in SQL queries, constraints, and data manipulation.