Assignment Solutions

Q. Create Database as SQL_practice and use it for further questions.

A. create database sql_ practice;

Use sql_ practice;

Q. Create a table named "Students" with the following columns: StudentID (int),

ills
FirstName (varchar), LastName (varchar), and Age (int). Insert at least three records into
the table.

A. CREATE TABLE Students (

Sk
StudentID int PRIMARY KEY,

FirstName varchar(50),

LastName varchar(50),

Age int

);
a
at
INSERT INTO Students (StudentID, FirstName, LastName, Age)

VALUES
D

(1, 'John', 'Doe', 20),

(2, 'Jane', 'Smith', 22),

(3, 'Michael', 'Johnson', 21);

w
ro

Q. Update the age of the student with StudentID 1 to 21. Delete the student with
StudentID 3 from the "Students" table.

A. UPDATE Students
G

SET Age = 21

WHERE StudentID = 1;

DELETE FROM Students

WHERE StudentID = 3;
 Q. Retrieve the first names and ages of all students who are older than 20.

A. SELECT FirstName, Age

FROM Students

WHERE Age > 20;

Q. Delete records from the same table where age<18.

A. Delete from Students

ills
WHERE Age <18 :

Q. Create a table named "Customers" with the following columns and constraints:

Sk
CustomerID (int) as the primary key.

FirstName (varchar) not null.

LastName (varchar) not null.

a
Email (varchar) unique.

Age (int) check constraint to ensure age is greater than 18.

at
A. CREATE TABLE Customers (

CustomerID int PRIMARY KEY,

D

FirstName varchar(50) NOT NULL,

LastName varchar(50) NOT NULL,

w

Email varchar(100) UNIQUE,

Age int CHECK (Age > 18)

ro

);
G

Q. You have a table named "Orders" with columns: OrderID (int), CustomerID (int),
OrderDate (date), and TotalAmount (decimal). Create a foreign key constraint on the
"CustomerID" column referencing the "Customers" table.

A. ALTER TABLE Orders

ADD CONSTRAINT FK_Customer

FOREIGN KEY (CustomerID)

REFERENCES Customers(CustomerID);
 Q. Create a table named "Employees" with columns:

EmployeeID (int) as the primary key.

FirstName (varchar) not null.

LastName (varchar) not null.

Salary (decimal) check constraint to ensure salary is between 20000 and 100000.

A. CREATE TABLE Employees (

ills
EmployeeID int PRIMARY KEY,

FirstName varchar(50) NOT NULL,

LastName varchar(50) NOT NULL,

Sk
Salary decimal CHECK (Salary BETWEEN 20000 AND 100000)

);

a
Q. Create a table named "Books" with columns:

BookID (int) as the primary key.

at
Title (varchar) not null.

ISBN (varchar) unique.

D

A. CREATE TABLE Books (

BookID int PRIMARY KEY,

w

Title varchar(100) NOT NULL,

ISBN varchar(20) UNIQUE

ro

);
G

Q. Consider a table named "Employees" with columns: EmployeeID, FirstName,

LastName, and Age. Write an SQL query to retrieve the first name and last name of
employees who are older than 30.

A.SELECT FirstName, LastName

FROM Employees

WHERE Age > 30;

 Q. Using the same "Employees" table, write an SQL query to retrieve the first name, last
name, and age of employees whose age is between 20 and 30.

A. SELECT FirstName, LastName, Age

FROM Employees

WHERE Age BETWEEN 20 AND 30;

Q. Given a table named "Products" with columns: ProductID, ProductName, Price, and
InStock (0- for out of stock, 1- for in stock). Write an SQL query to retrieve the product

ills
names and prices of products that are either priced above $100 or are out of stock.

A. SELECT ProductName, Price

FROM Products

Sk
WHERE Price > 100 OR InStock = 0;

Q. Using the "Products" table, write an SQL query to retrieve the product names and
prices of products that are in stock and priced between 50 and 150.
a
A. SELECT ProductName, Price
at
FROM Products

WHERE InStock = 1 AND Price BETWEEN 50 AND 150;

D

Q. Consider a table named "Orders" with columns: OrderID, OrderDate, TotalAmount,

and CustomerID. Write an SQL query to retrieve the order IDs and total amounts of
w

orders placed by customer ID 1001 after January 1, 2023, or orders with a total amount
exceeding $500.
ro

A. SELECT OrderID, TotalAmount

FROM Orders

WHERE CustomerID = 1001 AND OrderDate > '2023-01-01' OR TotalAmount > 500;
G

Q. Retrieve the ProductName of products from the "Products" table that have a price
between $50 and $100.

A. SELECT ProductName

FROM Products

WHERE Price BETWEEN 50 AND 100;

 Q. Retrieve the names of employees from the "Employees" table who are both from the
"Sales" department and have an age greater than 25, or they are from the "Marketing"
department.

A.SELECT Name

FROM Employees

WHERE (Department = 'Sales' AND Age > 25) OR Department = 'Marketing';

ills
Q. Retrieve the names of customers from the "Customers" table who are not from the
city 'New York' or 'Los Angeles'.

A.SELECT Name

Sk
FROM Customers

WHERE City NOT IN ('New York', 'Los Angeles');

a
Q. Retrieve the names of employees from the "Employees" table who are either from the
"HR" department and have an age less than 30, or they are from the "Finance"
at
department and have an age greater than or equal to 35.

A.SELECT Name
D

FROM Employees

WHERE (Department = 'HR' AND Age < 30) OR (Department = 'Finance' AND Age >=
35);
w

Q. Retrieve the names of customers from the "Customers" table who are not from the
ro

city 'London' and either have a postal code starting with '1' or their country is not 'USA'.

A. SELECT Name

FROM Customers
G

WHERE City <> 'London' AND (PostalCode LIKE '1%' OR Country <> 'USA');