Web Development and Database Administration
Level IV Advanced Structured Query Language
lab work
Lab 1 Operation Sheet-1.1 Create database
Operation title: Create a Merchant database
Purpose: To create a merchant database with customer, supplier, customer_order, orderItem and
product table their respective relationships
Equipment tools and materials: SQL server 2012
Step 1: Create database merchant;
Step 2: create customer table
In order to create customer table, we will write the following SQL statement
CREATE TABLE Customers (
Id int identity primary key,
FirstName nvarchar(40) not null,
LastName nvarchar(40) not null,
City nvarchar(40) null,
Country nvarchar(40) null,
Phone nvarchar(20) null)
Step 3: Insert into customers table
INSERT INTO [Customers] ([Id],[FirstName],[LastName],[City],[Country],
[Phone])VALUES(1,'Maria','Anders','Berlin','Germany','')
INSERT INTO [Customers] ([Id],[FirstName],[LastName],[City],[Country],
[Phone])VALUES(2,'Ana','Trujillo','México D.F.','Mexico','(5) 555-4729')
INSERT INTO [Customers] ([Id],[FirstName],[LastName],[City],[Country],
[Phone])VALUES(3,'Antonio','Moreno','México D.F.','Mexico','(5) 555-3932')
INSERT INTO [Customers] ([Id],[FirstName],[LastName],[City],[Country],
[Phone])VALUES(4,'Thomas','Hardy','London','UK','(171) 555-7788')
INSERT INTO [Customers] ([Id],[FirstName],[LastName],[City],[Country],
[Phone])VALUES(5,'Christina','Berglund','Luleå','Sweden','0921-12 34 65')
INSERT INTO [Customers] ([Id],[FirstName],[LastName],[City],[Country],
[Phone])VALUES(6,'Hanna','Moos','Mannheim','Germany','0621-08460')
INSERT INTO [Customers] ([Id],[FirstName],[LastName],[City],[Country],
[Phone])VALUES(7,'Frédérique','Citeaux','Strasbourg','France','[Link]')
INSERT INTO [Customers] ([Id],[FirstName],[LastName],[City],[Country],
[Phone])VALUES(8,'Martín','Sommer','Madrid','Spain','(91) 555 22 82')
INSERT INTO [Customers] ([Id],[FirstName],[LastName],[City],[Country],
[Phone])VALUES(9,'Laurence','Lebihan','Marseille','France','[Link]')
INSERT INTO [Customers] ([Id],[FirstName],[LastName],[City],[Country],
[Phone])VALUES(10,'Elizabeth','Lincoln','Tsawassen','Canada','(604) 555-4729')
Step 4: To retrieve the customers table, we can write the following SQL statement
Select * from customers
Quality Criteria: your output should look like this
Step 5: Here we will create a supplier table (refer step 2)
Step 6: Insert values in to supplier table (refer step 3)
Step 7: To retrieve the supplier table, we can write the following SQL statement
Select * from supplier
Step 8: Here we will create Customer_order table
In order to create the customer_order table, we will write the following SQL statement
CREATE TABLE "customer_Order" (
Id int identity primary key,
OrderDate datetime not null default getdate(),
OrderNumber nvarchar(10) null,
CustomerId int not null foreign key references
customers (id)
TotalAmount decimal(12,2) null default 0,)
Constraint PK_ORDER primary key (Id)
)
Step 9: Insert values in to customer_order table (refer step 3)
Step 10: To retrieve the Customer_order table, we can write the following SQL statement
Select * from Customer_order
Quality Criteria: your output should look like this
Step 11: Here we will create OrderItem table (refer step 8)
Step 12: Insert values in to OrderItem table (refer step 3)
Step 13: To retrieve the OrderItem table, we can write the following SQL statement
Select * from OrderItem
Quality Criteria: your output should look like this
Step 14: Create Product table (refer step 8)
Step 15: Insert values in to product table (refer step 3)
Step 16: To retrieve the Product table, we can write the following SQL statement
Select * from Product
Quality Criteria: your output should look like this
Step 17: Create index for the merchant database tables
create index IndexCustomerName on Customers (LastName, FirstName )
create index IndexSupplierCountry on Supplier (Country ASC)
create index IndexProductSupplierId on Product (SupplierId ASC)
create index IndexOrderItemProductId on OrderItem (ProductId ASC)
create index IndexCustomerorderId on "customer_Order" (CustomerId ASC)
Operation Sheet-1.2 where clause with comparison operators
Operation title: where clause with comparison operators
Purpose: To show functionalities of where clause with comparison operators
Equipment tools and materials: SQL server 2012
Step 1: Use the above merchant database operation sheet 1.1
Step 2: here we want to retrieve customers whose country is Mexico, so we can write the
following query
Select Id, FirstName, LastName, City, Country, Phone
FROM Customers
WHERE Country = 'Mexico'
Quality Criteria: your output should look like this
Step 3: here we want retrieve orders whose unit price is greater than 30, so we can write the
following SQL statement
Select * from OrderItem where unitprice>30;
Quality Criteria: your output should look like this
Step 4: here we want retrieve to list products with order quantities greater than or equal to 15, so
we can write the following SQL statement
SELECT ProductName
FROM Product
WHERE Id IN (SELECT ProductId
FROM OrderItem
WHERE Quantity >= 15)
Quality Criteria: your output should look like this
Operation Sheet-1.3 where and order by clause
Operation title: where and order by clause
Purpose: To show functionalities of where and order by clause
Equipment tools and materials: SQL server 2012
Step 1: Use the above merchant database from operation sheet 1.1
Step 2: here we want to retrieve 50% of the customers record, so we can write the following
SQL statement
Select top 5 * from customers where Country='mexico'
Quality Criteria: your output should look like this
Step 3: here we want to list all suppliers with the number of products they offer, so we can write
the following SQL statement
SELECT CompanyName,
ProductCount = (SELECT COUNT([Link])
FROM [Product] P
WHERE [Link] = [Link])
FROM Supplier S order by companyname DESC
Quality Criteria: your output should look like this
Step 4: here we want to list all French customer cities (without duplicates)
SELECT distinct city from customers where Country = 'france';
Quality Criteria: your output should look like this
Step 5: here we want to list all suppliers that have no fax, we can write the following query
SELECT Id, CompanyName, Phone, Fax
FROM Supplier
WHERE Fax IS NULL
Quality Criteria: your output should look like this
Operation Sheet-1.4 Working with Boolean operators
Operation title: Working with Boolean Operators
Purpose: To show functionalities of LIKE, EXISTS and IN operators
Equipment tools and materials: SQL server 2012
Step 1: Use the above merchant database from operation sheet 1.1
Step 2: Here we want to list all products that are packaged in jars, we can write the following
query
SELECT *
FROM Product
WHERE Package LIKE '%jars%'
Quality Criteria: your output should look like this
Step 3: here we want to list customers with orders over $2000, we can write the following query
SELECT *
FROM Customers
WHERE EXISTS
(SELECT Id
FROM [customer_Order]
WHERE CustomerId = [Link]
AND TotalAmount > 2000)
Quality Criteria: your output should look like this
Step 4: here we want to list customers who are from London or Paris, we can write the following
query
SELECT firstname
FROM Customers
WHERE City IN ('Paris','London')
Quality Criteria: your output should look like this
Operation Sheet-1.5 Working with Join
Operation title: Working with Join
Purpose: To show functionalities of Join, left join, right join and full join
Equipment tools and materials: SQL server 2012
Step 1: Use the above merchant database from operation sheet 1.1
Step 2: To List all suppliers with their products we can write the following query
SELECT CompanyName, ProductName
FROM Supplier S
JOIN Product P ON [Link] = [Link]
Quality Criteria: your output should look like this
Step 3: To list all suppliers and their products, including suppliers with no products we can write
the following query
SELECT CompanyName, ProductName
FROM Supplier S
LEFT JOIN Product P ON [Link] = [Link]
Quality Criteria: your output should look like this
Step 4: To list customers that have not placed orders we can write the following query
SELECT FirstName, LastName, City, Country, TotalAmount
FROM [customer_Order] O
RIGHT JOIN Customers C ON [Link] = [Link]
WHERE TotalAmount IS NULL
Quality Criteria: your output should look like this
Step 5: To match all customers and suppliers by country we can write the following query
SELECT [Link], [Link], [Link] AS CustomerCountry,
[Link] AS SupplierCountry, [Link]
FROM Customers C
FULL JOIN Supplier S ON [Link] = [Link]
ORDER BY [Link], [Link]
Quality Criteria: your output should look like this
Operation Sheet-1.6 Working with union operator
Operation title: Working with union operator
Purpose: To show functionalities of Join, left join, right join and full join
Equipment tools and materials: SQL server 2012
Step 1: Use the above merchant database from operation sheet 1.1
Task 1
Step 1: To list all unique countries for customers and suppliers we can write the following
statement
SELECT Country
FROM Customers
UNION
SELECT Country
FROM Supplier
Quality Criteria: your output should look like this
Operation Sheet-1.7 Data Control Language
Operation title: Data Control Language
Purpose: To show grant and revoke commands for a database user
Equipment tools and materials: SQL server 2012
Step 1: Use the above merchant database from operation sheet 1.1
Step 2: Imagine we have two database administrators ababe and kebede and we want them to
create a table, insert and delete a data from the tables from the merchant database. So, we can
write the following query (suppose that kebede will grant permission for ababe)
CREATE login ababe with password='123'
CREATE user ababe for login ababe
GRANT create table to ababe
CREATE login kebe with password='123'
CREATE user kebe for login kebe
GRANT insert,delete on database:: merchant to ababe
Step 3: Cancel ababe’s permission on the merchant database
REVOKE insert,delete on database:: merchant to ababe
Lab 2 Operation Sheet-2.1 Arithmetic operators and string function
Operation title: Arithmetic operators and string function
Purpose: To show functionalities of arithmetic operators and string functions in a table
Equipment tools and materials: SQL server 2012
Step 1: Use merchant database from operation sheet 1.1
Step 2: Calculate the total amount (UnitPrice * Quantity) for each order item and display the
result with the order item ID and the total amount.
SELECT Id, UnitPrice * Quantity AS TotalAmount
FROM OrderItem;
Quality Criteria: your output should look like this
Step 3: To retrieve the product names that start with the letter "A" and display them in uppercase.
SELECT UPPER(ProductName) AS ProductName
FROM Product
WHERE ProductName LIKE 'A%';
Quality Criteria: your output should look like this
Operation Sheet-2.2 Date function
Operation title: Date function
Purpose: To show functionalities of some date functions
Equipment tools and materials: SQL server 2012
Step 1: Use merchant database from operation sheet 1.1
Step 2: let us create a table called orders which stores orders of customers with their order id,
product name and the date, which the order is placed. We can write the table as shown below
CREATE TABLE Orders
(
OrderId int NOT NULL PRIMARY KEY IDENTITY,
ProductName varchar(50) NOT NULL,
OrderDate datetime DEFAULT GETDATE()
Step 3: to insert order data in the orders table we can write the following statement
INSERT INTO Orders1111 (ProductName) VALUES ('Shola Milk')
Quality Criteria: your output should look like this
Step 4: From the previous table, if we want to retrieve the date in year, month, date format, we
can write the following query
SELECT DATEPART(yyyy,OrderDate) AS OrderYear,
DATEPART(mm,OrderDate) AS OrderMonth,
DATEPART(dd,OrderDate) AS OrderDay
FROM Orders1111
WHERE OrderId=1
Quality Criteria: your output should look like this
Step 5: If we want to add 30 days to the "OrderDate", to find the payment date. We can write the
following query
SELECT OrderId,DATEADD(day,30,OrderDate) AS OrderPayDate
FROM Orders1111
Quality Criteria: your output should look like this
Lab 3 Operation Sheet-3.1 Sort aggregated data and backup
Operation title: Sort aggregated data and backup database
Purpose: To sort aggregated data using group by, order by and having clause. And to be able to
back up the database.
Equipment tools and materials: SQL server 2012
Step 1: Use merchant database from operation sheet 1.1
Step 2: To retrieve the total order amount for each customer and sort the result in descending
order of the sum.
SELECT CUST_CODE, SUM(ORD_AMOUNT) AS TotalOrderAmount
FROM ORDERS
GROUP BY CUST_CODE
ORDER BY TotalOrderAmount DESC;
Quality Criteria: your output should look like this
Step 3: To list the number of products for each supplier, sorted high to low.
SELECT [Link], COUNT([Link]) AS Products
FROM Supplier S
JOIN Product P ON [Link] = [Link]
GROUP BY [Link]
ORDER BY COUNT([Link]) DESC
Quality Criteria: your output should look like this
Step 4: List all countries with more than 1 suppliers.
SELECT Country, COUNT(Id) AS Suppliers
FROM Supplier
GROUP BY Country
HAVING COUNT(Id) > 1
Quality Criteria: your output should look like this
Step 5: To perform full back up the merchant database we can write the following query (but
first we have to create backups folder in the specified location)
BACKUP DATABASE merchant
TO DISK = 'D:\backups\[Link]';