1.
create database Human_resource
use Human_resource
2. we create three tables a,b,c respectivly
a. create table Department(DepName varchar(15),DepId int primary
key)
b. create table Job_position(jop_title varchar(25), job_id int
primary key,RequiredExperince varchar(25),DepId int foreign
key references Department(DepId), salary decimal(12,2))
c. create table Employee(Empname varchar(15),empId int primary key,
BDate date,sex varchar(5),job_id int foreign key references
Job_position(job_id))
3. Insert data to all tables
4. Write a Query that displays employee name, job title, salary and department name of female
employee
5. Display employees whose date of birth is less than 1995 and greater than 1987 and who works
in systemadmin
6. Update the salary of employee by adding 1000 which salary is below 4000
7. Display employees id ,name,department name and salary and display the result in descending
order of salary
8. Retrieve the list of department with job position where salary is greater than or equal to 5000?
9. write a query which would display all the employee records where the name starts with M and
is at least 7 characters in length
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 2008 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','88.60.15.31')
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','91.24.45.40')
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: see your output
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
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
