SQL - Interacting with databases through SSMS

Anjana A Feb 08 2021 · 4 min read
Share this

This article explores the basic functions used to interact with databases in SQL Server using SSMS through five hands-on assignments.

                                                    Assignment - 1

Problem Statement

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

‘last_name’, ‘email’, ‘address’, ‘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


create database Customerinfo


use Customerinfo

create table Customer (customer_id int,

first_name varchar (10),

last_name varchar (10),

email varchar (35),

address_ varchar (50),

city varchar (25),

state_ varchar (15),

zip varchar (10))


select * from Customer

insert into Customer (customer_id, first_name,last_name,email,address_, city, state_, zip)

values (1, 'Emily', 'Cooper', '[email protected]','Duval County', 'Jackson Ville','Florida', '32099'),

(2,'Sylvie','Savoir', '[email protected]', 'Île-de-France', 'Paris', 'France', '75001' ),

(3, 'Mindy', 'Chen', '[email protected]', 'Île-de-France','Paris', 'France', '75020'),

(4, 'Julien','Harris','[email protected]','Hauts-de-Seine','Nanterre','France','75002'),

(5,'Gluc', 'Flynn', '[email protected]', 'Seine-et-Marne','San Jose','California','75004')



select first_name, last_name from Customer

select * from customer

where first_name like 'G%' and city = 'San Jose';

                                                            Assignment -2 

Problem Statement

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 3

Solution

use Customerinfo

create table Orders (Order_Id varchar(15),

Order_date date,

Amount float(15),

customer_id int)

insert into orders(order_id,order_date,amount,customer_id)

values ('33','2020-07-14','50','1'),

('34','2020-04-30','58','2'),

('35','2020-05-06','50','3');

select * from Orders




select * from Orders ORD inner join Customer CUS on ORD.customer_id = CUS.customer_id



select * from Orders ORD left join Customer CUS on ORD.customer_id = CUS.customer_id



select * from Orders ORD right join Customer CUS on ORD.customer_id = CUS.customer_id


update Orders

set Amount = '100'

where customer_id = '3'

select * from Orders

                                                               Assignment - 3

Problem Statement

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 100 is less than 200, greater than 200 or equal to 2oo and

print the corresponding value

Solution

select MIN(amount) as min_amount

from orders


select Max(amount) as max_amount

from orders


select avg(amount) as avg_amount

from orders

GO

create function mult (@num int)

returns int as

Begin

SET @num = @num*10

Return @num

End;

GO

select *,dbo.mult(Amount) as multiplied_by_10

from orders


select order_id, amount,

Case when amount > 200 then 'Amount is greater than 200'

when amount < 200 then 'Amount is less than 200'

when amount = 200 then 'Amount is equal to 200'

End as Amount_status from orders

                                                                      Assignment - 4

Problem Statement

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

select * from orders

order by

amount desc;



create table employee_details1 (emp_id int, emp_name varchar(20), salary int)

create table employee_details2 (emp_id int, emp_name varchar(20), salary int)

insert into employee_details1(emp_id,emp_name,salary)

values (1,'Jennifer',540000),

(2,'Saniya',230000),

(3,'Karen',970000);

insert into employee_details2(emp_id,emp_name,salary)

values (1,'Janaki',430000),

(4,'Nicola',540000),

(3,'Larry',390000);

select * from employee_details1

Union

select * from employee_details2

select * from employee_details1

except

select * from employee_details2

                                                                 Assignment - 5

Problem statement

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

from san jose

2. Inside a transaction, update the first name of the customer to Francis, where the last name is

Jordan

a. Rollback the transaction

b. Set the first name of customer to Alex, where the last name is Jordan

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

Solution

GO

create view [Customer_San_Jose] as

select * from customer where city='San Jose'

GO

select * from Customer_San_Jose

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

begin transaction Update_Name
update Customer
set first_name = 'Francis'
where last_name = 'Jordan' and customer_id = 3

select * from Customer where customer_id = 3
select * from Customer
begin transaction Update_Name
update Customer
set first_name = 'Francis'
where last_name = 'Jordan' and customer_id = 3
rollback transaction Update_Name

select * from Customer where customer_id = 3

update Customer
Set first_name = 'Alex'
where last_name = 'Jordan'

* * *
Comments
Read next