SQL Query Assignments

--------------------------

ASSIGNMENT - 1

---------------------------

Create the following tables using SQL Server Management Studio.

Table Name : flatMaster

About this table:

The flatMaster table is a master table which holds all the details related to all the flats in the apartment. This table has got flatNo as primary key as there is no duplicate flat numbers in the apartment. Flat numbers are written/captured in such a way that they give meaning to block number and flat number. A101 gives the meaning as , this belongs to block 'A' and then number of the flat is 101.

Field Name	Field Type	Constraints (if any)	Description
flatNo	varchar(10)	PRIMARY KEY	This field contain the flat number. This would an alphanumeric value. For example : A303 (means block A and flat no. 303)
floorNo	smallint	NA	Floor number. For example : 1 , 2, 3
block	varchar(20)	NA	This is the name of the block. For example : 1 , ‘A’, etc.
areasqft	float	NA	Example : 1100 etc.
comments	varchar(100)	NA	Any additional comments

Table Name : residents

About this table:

Table residents is the one which holds all the residents details.

Field Name	Field Type	Constraints (if any)	Description
esidentID	smallint	Primary Key	This is the ID of residents. Example: 1,2 ,3 etc.
residentName	Varchar(80)	NA	Name of the resident. Example : John Stone
flatNo	Varchar(10)	Foreign key to flatMaster(flatNo)	Stores the flat number of the resident.
phone	varchar(10)	NA	Phone number of the resident
DOB	datetime	Age at least 18 Years. (Difference of DOB and the current data should not be less than 18)	Date of Birth of the resident
email	varchar(50)	NA	Email of the resident.
lastEditAt	datetime	NA	Last edit date
lastEditComments	varchar(50)	NA	Last edit comments

Table Name: users

About this table:

All the residents who are users also. They will login into the apartment system. This table has got all the users details. Residents will have userID & password (i.e. pwd) using which they can login into the system. The moment user goes out or no more the part of the apartment , isActive field can be turned into 'N' instead of 'Y'.

Field Name	Field Type	Constraints (if any)	Description
userID	smallint	Foreign key reference to residentID of residents table	Holds the userID of the users login to the system
userName	varchar(50)	Primary Key	User Name of the users. This is unique in nature.
pwd	varchar(50)	NA	Holds password
isActive	char(1)	Value should be ‘Y’ or ‘N’	Holds yes (‘Y’) or No (‘N’).
dtCreated	datetime	Default Current Date	Holds the date of creation
lastEditAt	datetime	NA	Holds the data when last edit took place
lastEditComments	varchar(100)	NA	Holds the last edit comments.

Table Name : maidMaster

About this table:

Household, working staffs are maids. This table is the master table for the maids. This holds all the maid details.

Field Name	Field Type	Constraints (if any)	Description
maidID	Int	PRIMARY KEY	Holds the maid ID. The values can be 1, 2, 3…
maidName	varchar(100)	NA	Holds the name of the maid. Example : Sumit Agarwal
experience	vharchar(200)	NA	Names of the skills (comma separated). Example : Cleaning, Kitchen Work , Baby sitting
isActive	char(1)	Value must be either ‘Y’ or ‘N’	Holds the value ‘Y’ or ‘N’. If the maid is active or not
dtJoined	Datetime	NA	Date when the maid joined the apartment.

Table Name : maidFlatLink

About this table:

As the name suggests , this table will hold the link details of a maid and the flats. One maid can have multiple flat association. This also maintains the ratings of the maids and helpful for others to take decision about which maid is to be selected for their household activities.

Field Name	Field Type	Constraints (if any)	Description
flatNo	varchar(10)	NA	Holds the flat number where the maid works
maidID	smallint	Foreign key to maidMaster(maidID)	Holds the id of the main. For example : 1,2 etc.
workingSince	datetime	NA	Hold the date since when the maid is working with flats
ratings	smallint	CHECK constraints where the value of ratings should range from 1 to 5	Holds the ratings of the main. The value can be 1 to 5.
comments	varchar(100)	NA	Comments if any. For example : Good behaviour etc.

INSERT 10 records into each of the above tables. Below data can be used to insert the record into the tables.

Data for the table flatMaster

Data for the table residents:

Data for the table users:

Data for the table maidMaster:

Data for the table maidFlatLink table

Let's move to the second assignment (Assignment - 02) once the above is done.

--------------------------

ASSIGNMENT - 02

---------------------------

Write SQL Queries for the following (based on the above data). You can use SQL Server Management Studio to run your queries:

Hint/Note : You will be using two features here. [1]. use of aliases [2]. use of DISTINCT keyword.

Select (display) all the details of all the residents
Select (display) floor numbers from the flatMaster table. (What are all the flats?)
Select (display) residentName, phone and email of all the residents
Select (display) all the flat details.
Select (display) resident's Name, Date of Birth and flat they belong to
Select (display) residentName as (Resident Name), DOB as (Date Of Birth) , flatNo (Flat Residing In)
Display unique floors
Display unique blocks
What are all distinct size of flats available
Select (display) all users names with their respective passwords

Let's understand both of the above concepts i.e. aliases and DISTINCT keyword.

SQL Aliases

SQL aliases are used to give a table, or a column in a table, a temporary name. Aliases are often used to make column names more readable. An alias only exists for the duration of that query. An alias is created with the AS keyword.

Examples :

SELECT DOB as 'Date Of Birth' , residentName as 'Name of The Resident' from resident

SELECT residentName As [Resident's Name] FROM residents

If the alias name is a single word we can use them as it is :

SELECT block as Block FROM flatMaster

DISTINCT Keyword

The SELECT DISTINCT statement is used to return only distinct (different) values. Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.

Example :

SELECT DISTINCT floorNo from flatMaster

--------------------------

ASSIGNMENT - 03

---------------------------

Write SQL Queries for the following (based on the above data). You can use SQL Server Management Studio to run your queries:

Hint/Note : You will be using two features here. [1]. WHERE Clause [2]. Like Keyword.

Select (Display) all the flats belonging to 'A' Block.
Select (Display) all the 3rd floor flats
Select (Display) all the active users
Select (Display) all the residents whose names start with English letter 'a' or 'A'
Select (Display) all the residents whose names end with English letter 'a' or 'A'
Select (Display) all the residents whose names second character English letter 'a' or 'A'
Select (Display) all the residents whose names third character English letter 'i'
Select (Display) all the residents whose names contain the word 'kumar'
Select (Display) all the residents whose names First character is 'a' and second character can be anything in [m,j]
Select (Display) all the residents whose names first character is 'a' but the second character is neither 'm' nor 'j' (but can be anything else)
select (Display all the residents who have got gmail ID

Let's understand both of the above concepts i.e. WHERE Clause and Like keyword.

WHERE Clause

The WHERE clause is used to filter records. It is used to extract only those records that fulfill a specified condition.

Example :

SELECT * FROM residents WHERE flatNo='A001'

Note: The WHERE clause is not only used in SELECT statements, it is also used in UPDATE, DELETE, etc. (We will be talking about this in coming assignments and topics).

There are many operators which can be used along with WHERE clause. These operators can be used to select the records based on conditions.

Below are the list of operators :

SL No.	Operators		Description
1		=	Equal
2		>	Greater Than
3		<	Less Than
4		>=	Greater than or equal
5		<=	Less than or equal
6		<>	Not Equal
7		BETWEEN	Between the specified range
8		LIKE	Comparing/Searching for a pattern
9		IN	To specify multiple possible values

LIKE Keyword/Clause/Operator

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards often used in conjunction with the LIKE operator:

The percent sign (%) represents zero, one, or multiple characters
The underscore sign (_) represents one, single character

Below are some of the symbols which we use along with LIKE clause and in conjunction with WHERE.

SL No.	Symbol		Description	Example
1		%	Represents zero or more characters	aj% finds aj, ajit, ajay, and ajju etc.
2		_	Represents a single character	s_m finds sum, sam, sym etc.
3		[ ]	Represents any single character within the brackets	h[oa]t finds hot and hat, but not hit
4		^	Represents any character not in the brackets	h[^oa]t finds hit, but not hot and hat
5		-	Represents any single character within the specified range	c[a-b]t finds cat and cbt

--------------------------

ASSIGNMENT - 04

---------------------------

Write SQL Queries for the following (based on the above data). You can use SQL Server Management Studio to run your queries:

Hint/Note : You will be using the features here. [1]. AND, OR & NOT

Select (Display) all the residents who were born before 1970.
Select (Display) all the flats belonging to block 'A' and area in square feet is less than 1300
Select (Display) all residents who resides either in Block A or her/his names starts with 'A' ( No JOIN required)
Select (Display) all residents who born between 1960 and 1970
Select (Display) all residents who are not of block 'A'
Select (Display) all flats belonging to block 'A' and not ground floor
Select (Display) all residents whose phone number starts with 9 and having gmail account
Select (Display) all the flat details which are more than 1200 sqft but less than 1350 sqft
Select (Display) all the flats on second floor but not in block 'B'
Select (Display) all the active users created in the year 2022

Let's understand both of the above concepts i.e. AND, OR & NOT.

AND, OR , NOT

The WHERE clause can be combined with AND, OR, and NOT operators..The AND and OR operators are used to filter records based on more than one condition:

The AND operator displays a record if all the conditions separated by AND are TRUE.

The OR operator displays a record if any of the conditions separated by OR is TRUE.

The NOT operator displays a record if the condition(s) is NOT TRUE.

--------------------------

ASSIGNMENT - 05

---------------------------

Write SQL Queries for the following (based on the above data). You can use SQL Server Management Studio to run your queries:

Hint/Note : You will be using the features here IN, BETWEEN & TOP

Select (Display) all the floor details for the ground , first and second floor
Select (Display) all the floor details except for the ground , first and second floor
Select (Display) all the floor details having area in square feet between 1000 to 1300
Select (Display) all the floor details having area in square feet NOT between 1000 to 1300
Select (Display) all the residents details who were born between 1970 to 1980
Select (Display) top 1 residents details
Select (Display) top 50% residents details
Select (Display) top 2 residents who resides in block 'A'
Select (Display) all residents whose name starts from letter 'A' - 'P'
Select (Display) all users whose credentials created between January 2021 to December 2022

Let's understand both of the above concepts i.e. IN, BETWEEN & TOP.

The IN operator allows you to specify multiple values in a WHERE clause. All the values in IN should be written within bracket (small bracket '(' ')') and separated with commas. The IN operator is a shorthand for multiple OR conditions.

Example:

SELECT * FROM flatMaster WHERE floorNo IN(0,1,2)

BETWEEN

The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates. The two range values are written in conjunction with AND Operator.

Note: The BETWEEN operator is inclusive: begin and end values are included.

Example:

SELECT * FROM flatMaster WHERE areasqft NOT BETWEEN 1000 AND 1300

TOP

The SELECT TOP clause is used to specify the number of records to return. The SELECT TOP clause is useful on large tables with thousands of records. Returning a large number of records can impact performance.

Note: The TOP clause can also be used with PERCENT. This helps selecting the percentage of values from the row set.

Example:

SELECT TOP 2 * FROM residents

SELECT TOP 50 PERCENT FROM residents

Search This Blog

CodeWithAbby

SQL Query Assignments

Comments

Post a Comment