Question

Can anyone please help me with these SQL Server Questions? I am using SQL Server 2008

1. Write the code to create two table with at least 10 fields of various data types.

2. Write the code to insert 20 rows of data into the table that you created.

3. Write the code – “Select” statements to write three different reports from your data.

4. Write the code to create a report that incorporates functions.

5. Write the code to create two different views from the data that you created.

Answer 1

Dear Student ,

As per the requirement submitted above , kindly find the below solution.

This demonstration is using SQL Server.Database and tables are created using SQL Server Management Studio (SSMS).

Database :

/*create database TrainingDB*/
create database TrainingDB;

/*use TrainingDB to create table*/
use TrainingDB;

Tables :

1.Table Name :Trainers

SQL Query :

/*1.Table Name : Trainers*/
Create table Trainers(
TrainerID int primary key,
TrainerName varchar(50) not null,
Experience decimal(4,2) not null check (Experience > 2),
skills varchar(100) not null,
address text
);

/*Inserting records into Trainers*/
insert into Trainers values(1,'Virat Kohli',3,'Cricket coaching','Pune');
insert into Trainers values(2,'Sachin Tendulkar',18,'Cricket coaching','Mumbai');
insert into Trainers values(3,'Mahesh Sabnis',18,'Angular 7','Chennai');
insert into Trainers values(4,'Ajinkya Rahane',5.6,'MongoDB','Hyderabad');
insert into Trainers values(5,'KL Rahul',2.2,'Responsive web Design','Bangluru');
insert into Trainers values(6,'Sanju Samson',2.2,'Node.js','Kochi');
insert into Trainers values(7,'Eoan Morgan',7.7,'Node with ExpressJS','Jaipur');
insert into Trainers values(8,'Hardik Pandey',4.4,'Full Stack','Badoda');
insert into Trainers values(9,'KP',2.1,'Mean Stack','Delhi');
insert into Trainers values(10,'Rahul Dravid',15.1,'DBA','Kolkatta');

/*selecting records*/
select * from Trainers;

Screen in SSMS:

*********************************

2.Table Name :

SQL Query :

/*2.Table Name :ClientTrainings*/
create table ClientTrainings (
TrainingsID int ,
TrainingName varchar(100) not null ,
TrainerID int not null,
Client varchar(20) not null,
IsAvailable char(4) not null,
TrainingDate date,
primary key (TrainingsID),
foreign key (TrainerID) references Trainers(TrainerID)
);

/*insert records into Trainings table*/
insert into ClientTrainings values (1,'Getting Started with Angular 7',3,'Microsoft USA','Yes','12/05/2019');
insert into ClientTrainings values (2,'Cricket Speech',2,'NCA Bangluru','No','01/05/2019');
insert into ClientTrainings values (3,'Angualr 5',2,'NCA Bangluru','Yes','05/05/2019');
insert into ClientTrainings values (4,'JavaScript Basics',1,'NCA Bangluru','NO','02/10/2018');
insert into ClientTrainings values (5,'MongoDB',1,'Fujitsu Pune','Yes','03/11/2018');
insert into ClientTrainings values (6,'Mean Stack',9,'HCL Chennai','NO','02/12/2017');
insert into ClientTrainings values (7,'Docker Contrainer',10,'JP Mogran Mumbai','Yes','04/02/2019');
insert into ClientTrainings values (8,'.NET',7,'Sama Software Nashik','Yes','06/06/2019');
insert into ClientTrainings values (9,'Database',8,'SKS Software Pune','Yes','02/10/2018');
insert into ClientTrainings values (10,'Full stack Development',1,'C2L Solutions','Yes','07/07/2019');

/*selecting records*/
select * from ClientTrainings;

Screen in SSMS:

*********************************

Question 3.

SQL Query :

/*SQL Query that returns TrainingName 'Angualr 7','Cricket Speech' */
select Trainers.TrainerID,TrainerName,Experience,TrainingName
from Trainers,ClientTrainings where Trainers.TrainerID=ClientTrainings.TrainerID
order by Trainers.TrainerID

Screen in SSMS :

*********************************

Question 3.2:

SQL Query :

/*SQL Query that returns Trainer Id, Trainer Name , Trainings Name for given date */
select Trainers.TrainerID,TrainerName,TrainingName,TrainingDate
from trainers, Clienttrainings
where
trainers.trainerID=Clienttrainings.TrainerID and
TrainingDate='05/05/2019';

Screen in SSMS :

*******************************

Select all records from ClientTrainings table:

select * from clientTrainings;

Screen in SSMS :

******************************

Question 4 :

SQL Query :

/*create function to get Trainners details*/
create function getTrainderDetails()
returns table
as
return (select * from Trainers)

/*call function*/
select * from getTrainderDetails();

Screen in SSMS :

*******************************

Question 5 :

SQL Query :

--!create view to get all available trainers id
create view availableTrainer
as
select TrainerID from ClientTrainings where IsAvailable='Yes'

--execute view
select * from availableTrainer;

Screen in SSMS :

NOTE : PLEASE FEEL FREE TO PROVIDE FEEDBACK ABOUT THE SOLUTION.