Different Types Of Queries With Example In SQL Server Database 2021

 

 

Here are the some of the basic command and their use in SQL Server 2021.
Before using these command we will try  the following commands and understand the syntax and functionality:

 

Alter table tbl_Student Add CGPA float;

 

Alter table tbl_Student Add B_status varchar(10);

 

Alter table tbl_Student drop column CGPA;

 

Alter table tbl_Student alter column B_status int;

 

ALTER TABLE tbl_Faculty alter column fac_address varchar(50) NOT NULL;

 

ALTER TABLE tbl_Faculty ADD CONSTRAINT UC_Fac UNIQUE (fac_id, fac_cnic);

 

ALTER TABLE tbl_Faculty ADD CONSTRAINT CHK_FacultyAge CHECK (fac_Age>=18 AND fac_address='Austria');

 

 

ALTER TABLE tbl_Faculty ADD PRIMARY KEY (fac_id);

 

ALTER TABLE tbl_Faculty ADD CONSTRAINT PK_Faculty PRIMARY KEY (fac_id, fac_cnic);

 

ALTER TABLE tbl_Faculty DROP CONSTRAINT PK_Faculty;

 

ALTER TABLE tbl_Faculty ADD CONSTRAINT df_Name DEFAULT 'John' FOR fac_name;

 

ALTER TABLE tbl_foreign ADD FOREIGN KEY (attr_name_fk) REFERENCES tbl_primary(attr_name_pk);

 

ALTER TABLE tbl_foreign ADD CONSTRAINT FK_nameOfConstraint FOREIGN KEY (attr_name_fk) REFERENCES tbl_primary(attr_name_pk);

 

 

Query 01

Create a query to display the job title and vacation hours of the employee whose hours are greater than 50
Select JobTitle,VacationHours From HumanResources.Employee Where VacationHours>50;

Query 02

Create a query to display the Gender, birthday and job title  for employee whose date of birth is 1969-1-29

select Gender,BirthDate,JobTitle from HumanResources.Employee where BirthDate='1969-1-29'

 

Query 03

Create a query to display the sales orderID   and unit price for all the sales which are not under the range of  1500 and 2850

select SalesOrderID,UnitPrice from Sales.SalesOrderDetail where UnitPrice  not between 1500 and 2850

Query 04

Display the employee job title, Gender and hire date of employees hired between February 20, 2007 and May 1, 2008. order the query in ascending order by hire date

select JobTitle,Gender,HireDate from HumanResources.Employee where HireDate between '2007-2-20' and '2008-5-1' order by HireDate

 

Query 05

Display the currency code of following countries Pakistan Rupees, saudi Riyal and singapore Dollar

select CurrencyCode from Sales.Currency where Name in ('Pakistan Rupee','Saudi Riyal' , 'Singapore Dollar')

 

 

Query 06

 

List the job title, hire date and vacation hours of the employee whose vacation hours are greater than 30. lable the column as job description, joining date and unpaid hours respectively

( humanresource.employee table)

select JobTitle 'Job Description',HireDate 'Joining Date', VacationHours 'Unpaid Hours' from HumanResources.Employee where VacationHours>30

Query 07

 

Display the first, middle and last name of the employees whose name starts with ‘D’

( person.person table)

select FirstName,MiddleName,LastName from Person.Person where FirstName like 'D%'

Query 08

 

Display the first name of the employee whose middle name is not null

select FirstName,MiddleName from Person.Person where MiddleName is not null

 

Query 09

 

Display the department id, name and groupname and sort the data in the descending order by groupname

select GroupName,DepartmentID,Name  from HumanResources.Department order by GroupName desc

Query 10

 

Display the names of all employees where third letter of their name is an A

SELECT FirstName,MiddleName,LastName FROM Person.Person WHERE FirstName LIKE '__A%'

Query 11

 

Display the names of all the employees that have two Ls in their name and have title of ‘Mr.’

SELECT Title, FirstName,MiddleName,LastName FROM Person.Person WHERE (FirstName LIKE '%LL%' or MiddleName LIKE '%LL%' or LastName LIKE '%LL%') AND Title = 'Mr.'

Query 12

 

Display the job title, nationalIDnumber, hire date for all the employees whose job title is marketing assistant, accounts manager, recruiter and their vacation hours are not equal to 10,15 and 50

select JobTitle,NationalIDNumber,HireDate,VacationHours from HumanResources.Employee where JobTitle in ('Marketing Assistant','Accounts Manager','Recruiter') and VacationHours not in (10,15,50)

Query 13

 

. Display the job title, vacation hours, vacation hrs  in days in a separate column named vacations in days

select JobTitle,VacationHours, VacationHours/24 as 'Vacations in days'from HumanResources.Employee