Scribd
Upload
33 views

create database Huma1

Uploaded by

Amanuel Kassa
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
33 views

create database Huma1

Uploaded by

Amanuel Kassa
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 3

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

You might also like