SQL Assignment

##sql ##ssms ##dataanalytics ##businessanalytics ##number

Akash Gupta 087 Feb 10 2021 · 3 min read
Share this

In this project I am going to explain my detailed approach to solve the given assignments. Do give  a read and provide your valuable feedback.

Assignment 1:

Tasks to be performed

1. Create a customer table which comprises of these columns – ‘customer_id’, ‘first_name’,

‘last_name’, ‘email’ , ‘addres’, ‘city’ , ’state’ , ’zip’

2. Insert 5 new records into the table

3. Select only the ‘first_name’ & ‘last_name’ columns from the customer table

4. Select those records where ‘first_name’ starts with “G” and city is ‘San Jose’

Solution:

We can use the following syntax for the execution of our tasks.

Creation of the table

 create table CustomerDetails (
customer_id int,
first_name varchar(50),
last_name varchar(50),
email varchar(100),
addres varchar(80),
city varchar(15),
states varchar(15),
zip int
)

Insertion of Values

INSERT INTO [dbo].[CustomerDetails](customer_id,first_name,last_name,email,addres,city,states,zip) VALUES
(501,'Gautum', 'Mukherjee' , '[email protected]','Alipore','Kolkata','West Bengal',700017),
(502,'Ajay','Nagar','[email protected]','Mukherjeenagar','Delhi','New Delhi',840017),
(503,'Gorang','Neil','[email protected]','Nock','San Jose','USA',5400018),
(504,'Gopal', 'Das' , '[email protected]','Beegi','San Jose','USA',5400019),
(505,'Akash', 'Gupta' , '[email protected]','Mominpore','Kolkata','West Bengal',700027);

Displaying the required fields

select first_name,last_name from CustomerDetails

Displaying the required information with conditions

select *
from CustomerDetails
where 
first_name Like 'G%' and
city = 'San Jose'
The table with inserted values
Displaying the first name and last name using the conditions
Customers whose names start with G and belong to San Jose

Assignment 2:

Tasks to be performed

1. Create an ‘Orders’ table which comprises of these columns – ‘order_id’, ‘order_date’, ‘amount’,

‘customer_id’

2. Make an inner join on ‘Customer’ & ‘Order’ tables on the ‘customer_id’ column

3. Make left and right joins on ‘Customer’ & ‘Order’ tables on the ‘customer_id’ column

4. Update the ‘Orders’ table, set the amount to be 100 where ‘customer_id’ is 503

Solution:

Creation of Table

create table orders (
order_id int,
order_date date,
amount int,
customer_id int
)

Inserting Values into tables

insert into orders (order_id,order_date,amount,customer_id) Values
(10001,'1990-05-14',10000,501),
(10002,'1991-02-21',50000,503),
(10003,'2003-02-15',60000,504),
(10004,'2003-06-21',90000,505)
Orders Table

Making an Inner Join

select first_name,last_name,city,states,order_id,
order_date,amount,cd.customer_id
from CustomerDetails cd inner join orders od
on cd.customer_id = od.customer_id
Result after inner join

Making a left join

select first_name,last_name,city,states,order_id,
order_date,amount,cd.customer_id
from CustomerDetails cd left join orders od
on cd.customer_id = od.customer_id
Result after left join

Updating the values in table

update orders
set amount = 100
where 
customer_id = 503
Updated table

Assignment 3:

Tasks to be performed:

1. Use the inbuilt functions and find the minimum, maximum and average amount from the orders table

2. Create a user-defined function, which will multiply the given number with 10

3. Use the case statement to check if Amount is less than 200 or greater than 200

Solution:

Finding minimum, maximum and average amount from the orders table 

select min(amount) as min_amount
from 
orders

select max(amount) as maximum_amount
from 
orders

select avg(amount) as avg_amount
from 
orders
Minimum Amount
Maximum Amount
Average Amount

Function for finding multiplication by 10:

GO
create function change (@number int)
returns int as 
Begin 

     SET @number = @number*10

     Return @number
End;
GO
select *,dbo.change(Amount) as multiplication_of_10
from orders
Required Function

Using the case statement:

select order_id,amount,customer_id,
case 
when amount > 200 then 'Greater than 200'
when amount < 200 then 'Less than 200'
else
'Equal to 200'
end as Amount_Log
from 
orders
Case Statement

Assignment 4:

Tasks to be performed

1. Arrange the ‘Orders’ dataset in decreasing order of amount

2. Create a table with name ‘Employee_details1’ and comprising of these columns – ‘Emp_id’,

‘Emp_name’, ‘Emp_salary’. Create another table with name ‘Employee_details2’, which

comprises of same columns as first table.

3. Apply the union operator on these two tables

4. Apply the intersect operator on these two tables

5. Apply the except operator on these two tables

Solution:

Arranging the orders table in ascending order:

select *
from 
orders
order by amount desc
Descending Order

Creation of two tables

create table Employee_Details1 (
emp_id int,
emp_name varchar(50),
emp_salary int
)


create table Employee_Details2 (
emp_id int,
emp_name varchar(50),
emp_salary int
);


insert into [dbo].[Employee_Details2](emp_id,emp_name,emp_salary) Values
(100,'Ramesh',10000),
(101,'Rakesh',40000),
(105,'Mike',600000),
(106,'Red',80000)



insert into [dbo].[Employee_Details2](emp_id,emp_name,emp_salary) Values
(100,'Ramesh',10000),
(101,'Rakesh',40000),
(102,'John',60000),
(103,'Ravi',70000)

Applying the union operator:

Union Operation

All the rows from both the tables excluding the duplicates have been returned

Applying intersect operator


select *
from [dbo].[Employee_Details1]
intersect
select *
from [dbo].[Employee_Details2]
Intersect Operator

All the entries which are common to both the tables have been returned

Applying the except operator:

select *
from [dbo].[Employee_Details1]
except
select *
from [dbo].[Employee_Details2]
Except Operation

The entries which are not present in the second but in the first table have been returned

Assignment 5:

Tasks to be Done:

1. Create a view named ‘customer_san_jose’ which comprises of only those customers who are

from san jose

2.Inside a try catch block, divide 100 with 0, print the default error message

Solution:

Creation of view:

Required View

Try Catch Statement:

GO
create Proc divide(@a int, @b int, @c int output)
as
Begin
Begin Try
set @c = @a/@b;
End Try
begin catch
Select ERROR_MESSAGE() AS ErrorMessage;            
end catch
end
GO
declare @d int
exec divide 100, 0, @d output;
print @d

This is the complete assignment.

I would like to thank the iNeuron team for their constant support and guidance.

Please provide your valuable feedback!

Comments
Read next