What Is Clauses of Select Statement ? How To Use Clauses For Queries | Database Engineering

 Objectives:

To guide you through execution of various select statement clauses for retrieval of

specific rows and columns from table.

Description:

The basic SELECT... FROM ...WHERE clauses can be used with a single table in a number of

ways.

1. EXPRESSIONS

You can create expressions, which are mathematical manipulations of the data in the table, or use

stored functions, such as SUM or AVG, to manipulate the chosen rows of data from the table.

2. FUNCTIONS

3. WILDCARDS

Wildcards may also be used in the WHERE clause when an exact match is not possible. Here, the

keyword LIKE is paired with wildcard characters and usually a string containing the characters

that are known to be desired matches. The wildcard character, %, is used to represent any

collection of characters.

4. DISTINCT VALUES

Sometimes when returning rows that don’t include the primary key, duplicate rows will be

returned. We can use distinct keyword prior to attribute name to return unique values.

5. IN AND NOT IN WITH LISTS

To match a list of values, consider using IN. IN is particularly

useful in SQL statements that use subqueries.

6. RANGES FOR QUALIFICATION

The comparison operators < and > are used to establish a range of

values. The keywords BETWEEN and NOT BETWEEN can also

be used.

7. NULL VALUES

Columns that are defined without the NOT NULL clause may be

empty, and this may be a significant fact for an organization. You

will recall that a null value means that a column is missing a value;

the value is not zero or blank or any special code—there simply is

no value.

8. BOOLEAN OPERATORS

9. THREE BASIC CLAUSES IN SQL STATEMENTS.

Here are some of the example we will try to solve them using SQL Server:

1. Create another column in Student table named Adm_Date. Add the values given below against all available Student IDs. 

use task

Alter table student add Adm_Date date 

update student set Adm_Date='30 mar 2017' where studentID=38214; 

update student set Adm_Date='30 mar 2017' where studentID=54907;

 update student set Adm_Date='23 apr 2018' where studentID=70542; 

update student set Adm_Date='30 apr 2016' where studentID=66324;

 select * from student; 

OUTPUT:

2. Create another column in Faculty named Monthly Salary. Add the values of given below against all available faculty IDs. 

alter table Faculty add monthly_salary integer

 update Faculty set monthly_salary = 400 where FacultyID = 2143

 update Faculty set monthly_salary = 700 where FacultyID = 3467

 update Faculty set monthly_salary = 950 where FacultyID = 4756

 select * from Faculty;

 OUTPUT:

3. Create a query to display the Faculty ID, Faculty Name and annual salary and rename column as Instructor_ID, Instructor_Name, Annual_Salary from the Faculty table. 

select FacultyID as Instructor_ID, FacultyName as Instructor_Name,monthly_salary as Annual_Salary from Faculty 

OUTPUT: 

4. Write a query that displays student names with the first letter capitalize and all other letters lowercase and length of the names, for all students whose name start with ‘A’ or ‘B’. Give each column an appropriate label. 

Select concat(upper(SUBSTRING(StudentName,1,1)), lower(substring(StudentName,2,len(StudentName)-1))) as Name, len(StudentName) as lenght 

from Student where SUBSTRING(studentname,1,1) ='A' or SUBSTRING(studentname,1,1) ='B' 

OUTPUT:

5. Write SQL queries to answer the following questions: 1. Which students are enrolled in Database and Networking? (Hint: Use SectionNo for each class so you can determine the answer from the Registration table by itself.) 

select * from student s where s.StudentId in (select distinct r.StudentID from 

Registration r where r.SectionNo in (select distinct s.SectionNo from Section s where 

s.courseid in (select distinct c.CourseID from Course c where c.CourseName='database' 

or c.CourseName='networking'))) 

OUTPUT:

6. Which instructors cannot teach both Syst Analysis and Syst Design? 

select * from Faculty where FacultyID in (select q.FacultyID from Qualified q where 

q.CourseID not in(select c.CourseID from course c where c.CourseName ='Syst Analysis' or c.CourseName ='Syst Design' )) 

OUTPUT: