SQL Queries - Assignment-5

 Assignment - 5

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

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 

  1. Select (Display) all the floor details for the ground , first and second floor
  2. Select (Display) all the floor details except for the ground , first and second floor
  3. Select (Display) all the floor details having area in square feet between 1000 to 1300
  4. Select (Display) all the floor details having area in square feet NOT between 1000 to 1300
  5. Select (Display) all the residents details who were born between 1970 to 1980
  6. Select (Display) top 1 residents details 
  7. Select (Display) top 50%  residents details 
  8.  Select (Display) top 2 residents who resides in block 'A'
  9.  Select (Display) all residents whose name starts from letter 'A' - 'P'
  10. 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. 

IN
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

Comments

Post a Comment