SQL Part I Single Table Queries

1
SQL Part I --- Single Table Queries
2
Objectives

• Retrieve data from a database using SQL commands
• Use compound conditions
• Use computed columns
• Use the SQL LIKE operator
• Use the SQL IN operator
• Sort data using the ORDER BY clause
• Sort data using multiple keys and in ascending
  and descending order
• Use SQL aggregate functions
• Use subqueries
• Group data using the GROUP BY clause
• Select individual groups of data using the HAVING
  clause
• Retrieve columns with null values

3
Constructing Simple Queries

• Important feature of DBMS is ability to answer a
  wide variety of questions about the data
• A query is a question represented in a way that
  the DBMS can understand
• Use the SELECT command to query a database

4
SELECT Statement

• Basic form of the SQL SELECT statement is called
  a mapping or a SELECT-FROM-WHERE block
• SELECT ltattribute listgt
• FROM lttable listgt
• WHERE ltconditiongt
• ltattribute listgt is a list of attribute names
  whose values are to be retrieved by the query
• lttable listgt is a list of the relation names
  required to process the query
• ltconditiongt is a conditional (Boolean) expression
  that identifies the tuples to be retrieved by the
  query

5
A Sample University Database

• CLASS (CR_NO, D_CODE, C_NO, INSTRUCTOR_SSN)
• COURSE (D_CODE, C_NO, TITLE, UNITS)
• DEPARTMENT (D_CODE, D_NAME, CHAIRMAN_SSN)
• STUDENT (SSN, S_NAME, STATUS, MAJOR)
• FACULTY (SSN, F_NAME, RANK,D_CODE)
• TRANSCRIPT (STUDENT_SSN, D_CODE,C_NO, GR)

6
Class Table
7
Course Table
8
Department Table
9
Student Table
10
Faculty Table
11
Transcript Table
12
Retrieving Certain Columns and Rows

• Retrieve specified columns of a table (including
  all rows)
• Q1 List the C_NO and title of all courses.
• SELECT C_NO, TITLE
• FROM COURSE
• No WHERE clause is needed, because the
  information of all courses is requested

13
Question 1
SELECT C_NO, TITLE FROM COURSE
14
Retrieving All Columns

• Use an asterisk () to indicate all columns in
  the SELECT clause
• Q2 List all the tuples from DEPARTMENT table.
• SELECT
• FROM DEPARTMENT
• Results will list all columns in the order in the
  description when the table was created
• Note you can list columns in SELECT clause to
  present columns in a different order

15
Question 2
SELECT FROM DEPARTMENT
16
Using a WHERE Clause

• WHERE clause is used to retrieve rows that
  satisfy some condition
• Q3 List the CR_NO, D_CODE, and C_NO of classes
  taught by the instructor with SSN 235456789?
• SELECT CR_NO, D_CODE, C_NO
• FROM CLASS
• WHERE INSTRUCTOR_SSN235456789
• A simple condition is in the form of
• ltattribute namegt ltcomparison opgt ltconstant valuegt
  
• Or
• ltattribute namegt ltcomparison opgt ltattribute namegt

17
Using a WHERE Clause (cont.)

• The comparison operator can be
• Character values in SQL are case sensitive
• Grove is different than grove

18
Question 3
SELECT CR_NO, D_CODE, C_NO FROM
CLASS WHERE INSTRUCTOR_SSN235456789
Final Result
19
Using Compound Conditions

• Compound conditions connect two or more simple
  conditions with AND, OR, and NOT operators
• AND operator shows results that all simple
  conditions are true
• OR operator shows results that any simple
  condition is true
• NOT operator reverses the truth of the original
  condition

20
Question 4 Compound Condition using AND
Q4 List the names of all assistant professors of
ISE department. SELECT F_NAME FROM
FACULTY WHERE RANKAssistant and D_CODEISE
Final Result
21
Question 5 Compound Condition using OR
Q5 For the students who either are undergraduate
students or major in MATH, list their SSNs and
names. SELECT SSN, S_NAME FROM STUDENT WHERE
STATUS 'Under' or MAJOR'MATH'
22
Question 5 Compound Condition using OR (cont.)
Final Result
23
Question 6-- Using Operator NOT
Q6 For the courses that are not offered by IFSC
department, list their D_CODE, C_NO, and
title. SELECT D_CODE, C_NO, TITLE FROM COURSE WH
ERE NOT (D_CODE IFSC)
D_CODE C_NO TITLE ---------- ----------
--------------------------- ENGL
200 Grammar ENGL 400
Technical Writing MATH 321
Calculus MATH 501 Advance
Algebra BUSI 620 Finance
BUSI 580 Accounting CS
702 Network Security CS
620 Algorithms CS 545
Computer Network CS 501
Operating System
24
Question 6
Q6 For the courses except those offered IFSC
department, list their D_CODE, CR_NO, and
tilte. SELECT D_CODE, C_NO, TITLE FROM COURSE WH
ERE D_CODE ltgt IFSC
D_CODE C_NO TITLE ---------- ----------
--------------------------- ENGL
200 Grammar ENGL 400
Technical Writing MATH 321
Calculus MATH 501 Advance
Algebra BUSI 620 Finance
BUSI 580 Accounting CS
702 Network Security CS
620 Algorithms CS 545
Computer Network CS 501
Operating System
25
Using the BETWEEN Operator

• Not an essential feature in SQL
• Same results can be obtained without it
• Does make certain SELECT commands simpler to
  construct
• BETWEEN operator is inclusive
• When using BETWEEN 2000 and 5000, values 2000
  or 5000 would be true

A Guide to SQL, Seventh Edition
26
Question 7
Q7 For the students who got a grade between 70
and 90 for a course offered by IFSC, list their
SSNs. SELECT STUDENT_SSN, GR FROM
TRANSCRIPT WHERE GRlt90 AND GRgt 70 AND
D_CODEIFSC
STUDENT_SS GR ----------- ----------
521090112 82 811111111
90
27
Question 7-- Using Operator BETWEEN
Q7 For the students who got a grade between 70
and 90 for a course offered by IFSC, list their
SSNs. SELECT STUDENT_SSN, GR FROM
TRANSCRIPT WHERE GR BETWEEN 70 AND 90
AND D_CODEIFSC
STUDENT_SS GR ----------- ----------
521090112 82 811111111
90
28
Alias -- Renaming Relations

• It is possible to rename the relation names
  within the query by giving them aliases.
• Example list all customer numbers in the
  customer table.
• SECLECT D_NAME
• FROM DEPARTMENT D

29
Alias- Renaming Attributes

• It is also possible to rename the relation
  attributes within the query by giving them
  aliases.
• Example list all customer numbers in the
  customer table.
• SECLECT CHAIRMAN_SSN CHSSN
• FROM DEPARTMENT

CHSSN -------------- 134400029
123456789 121212121 244400001 147777678
156676789
30
Using the LIKE Operator

• Used to retrieve data where there may not be an
  exact match using wildcards
• LIKE Central will retrieve data with those
  characters
• 3829 Central or Centralia
• Underscore _ represents any single character
• T_M for TIM or TOM or T3M

A Guide to SQL, Seventh Edition
31
Question 8 LIKE Operator

• Q8 List the D_NAME, D_CODE of the departments
  whose name has the word science.
• SELECT D_NAME, D_CODE
• FROM DEPARTMENT
• WHERE D_NAME LIKE Science

D_NAME D_CODE ------------
------------------ ---------- Information
Science IFSC Computer Science
CS
32
Question 9 _ Operator

• Q9 List the names of the faculty whose SSNs
  start with 1 and end with 9
• SELECT F_NAME, SSN
• FROM FACULTY
• WHERE TO_CHAR(SSN) LIKE 1_______9

F_NAME SSN ----------------- ---
---------- Jones 134400029 King
154456789 Korth
166678789 Fisherman 177776789 Jackson
156676789 Ford
123456789 Chan 100006789
33
Using the IN Operator

• IN operator examines where an attribute takes a
  value of a set of values.
• Q10 List the SSNs and names of students who
  major in either IFSC or CS.
• SELECT SSN, S_NAME
• FROM STUDENT
• WHERE MAJOR IN ('IFSC', 'CS')

SSN S_NAME ---------- --------------------
415671238 William 521090112 Adam 811111111
Clinton 711111111 Peter 300000000 Simpson
987611111 Linda 434343431 Henry 123459999
Mike
34
Exercise 1

• For each assistant professor, list his/her F_name
  and department code.
• List the D_CODE,C_NO, and title of the courses
  offered by ENGL department.
• List the names and SSNs of graduate students.
• List the names of undergraduate students who
  major in BUSI.
• For the students who earned at least 80 in some
  courses, list their SSNs.
• List the names and ranks of the faculty members
  whose F_name starts with F.
• List the SSNs of instructors who are teaching
  non-CS classes.

35
Sorting

• Use ORDER BY clause to list data in a specific
  order
• SELECT
• FROM TRANSCRIPT
• WHERE C_NO600
• ORDER BY GRADE

STUDENT_SSN D_CODE C_NO
GRADE --------------------- ----------
---------- ---------- 415671238 IFSC
600 50 521090112 IFSC
600 82 811111111 FSC
600 90
36
Sorting (cont.)

• Column on which data is to be sorted is the sort
  key
• Rows are sorted in ascending order by default
  unless another order is specified
• For descending order sort, use DESC
• Possible to sort data by more than one key

37
Sorting -- examples
STUDENT_SSN D_CODE C_NO
GRADE --------------------- ----------
---------- ---------- 415671238 IFSC
600 50 521090112 IFSC
600 82 811111111 FSC
600 90

• SELECT
• FROM TRANSCRIPT
• WHERE C_NO600
• ORDER BY GRADE
• SELECT
• FROM TRANSCRIPT
• WHERE C_NO600
• ORDER BY GRADE DESC

STUDENT_SSN D_CODE C_NO
GRADE --------------------- ----------
---------- ---------- 811111111 FSC
600 90 521090112 IFSC
600 82 415671238 IFSC
600 50
38
Sorting examples (cont.)

• SELECT
• FROM TRANSCRIPT
• ORDER BY D_CODE, GRADE DESC

STUDENT_SSN D_CODE C_NO GRADE -------------
------- ---------- ---------- ----------
711111111 CS 501 100 300000000
CS 501 86 600000001 ENGL
200 81 711111111 IFSC 614
97 300000000 IFSC 614
95 811111111 IFSC 600 90
521090112 IFSC 600 82
415671238 IFSC 600 50
811111111 MATH 321 98 811111111
MATH 501 95 521090112 MATH
321 80 521090112 MATH 501
73
39
Using Functions

• Aggregate functions calculate sums, averages,
  counts, minimum and maximum values to groups of
  rows

40
COUNT Function

• Counts the number of rows in a table
• Q10 Count the number of classes offered.
• SELECT COUNT()
• FROM CLASS
• Q11 Count the number of undergraduate students
• SELECT COUNT()
• FROM CLASS
• WHERE STATUSUnder

COUNT() ---------- 19
COUNT() ---------- 9
41
Using the SUM Function

• Used to calculate totals of columns
• Q12 What is the total of student grades of the
  course with D_CODEIFSC and C_NO600?
• SELECT SUM(GRADE)
• FROM TRANSCRIPT
• WHERE D_CODEIFSC and C_NO600

SUM(GRADE) ------------------ 222
42
Using the AVG Function

• Calculate the average of columns
• Q13 Calculate the average student grade of the
  courses with D_codeIFSC.
• SELECT AVG(GRADE)
• FROM TRANSCRIPT
• WHERE D_CODEIFSC

AVG(GRADE) ------------------ 82.8
43
Using the MAX Function

• Get the maximum value of a column
• Q14 What is the highest student grade for the
  course with D_CODEIFSC and C_NO600.
• SELECT MAX(GRADE)
• FROM TRANSCRIPT
• WHERE D_CODEIFSC and C_NO600

MAX(GRADE) ---------- 90
44
Using the MIN Function

• Get the min value of a column
• Q15 What is the lowest student grade for the
  course with D_CODEIFSC and C_NO600.
• SELECT MIN(GRADE)
• FROM TRANSCRIPT
• WHERE D_CODEIFSC and C_NO600

MIN(GRADE) ---------- 50
45
Using the DISTINCT Operator

• Used to ensure uniqueness in the data results
• Q16A List the SSNs of students who got 90 in at
  least one course.
• SELECT STUDENT_SSN,GR
• FROM transcript
• WHERE GRgt90
• Q16B Count the number of students who got 90 in
  at least one course.
• SELECT COUNT()
• FROM transcript
• WHERE GRADEgt90

STUDENT_SSN ----------- 811111111 811111111
811111111 711111111 711111111 300000000
COUNT() ---------- 6
46
Using the DISTINCT Operator

• Used to ensure uniqueness in the data results
• Q16A List the SSNs of distinct students who got
  an 90 in at least one course.
• SELECT distinct STUDENT_SSN
• FROM transcript
• WHERE GRADE gt90
• Q16B Count the number of distinct students who
  got an A in at least one course.
• SELECT COUNT(distinct student_SSN)
• FROM transcript
• WHERE GRADEgt90

STUDENT_SSN ----------- 811111111 711111111
300000000
COUNT() ---------- 3
47
Nesting Queries

• Some queries will take two or more steps to
  obtain desired results
• A subquery is an inner query placed inside
  another query
• Outer query can use results of the subquery to
  find its results

A Guide to SQL, Seventh Edition
48
Using IN

• Q17 Find the CR_NO and the instructors SSN of
  classes that are taught by a department chair.
• SELECT CR_NO, INSTRUCTOR_SSN
• FROM CLASS
• WHERE INSTRUCTOR_SSN IN (SELECT CHAIR_SSN
• FROM DEPARTMENT)

CR_NO INSTRUCTOR_SSN ----------
--------------------------- 50001
123456789 10002 134400029 30007
134400029 10007 134400029 40001
156676789 20002 244400001
49
Using IN

• Q18 Find the courses that are not offered
  currently.
• SELECT D_CODE,C_NO, TITLE
• FROM COURSE
• WHERE (D_CODE,C_NO) NOT IN (SELECT D_CODE,
  C_NO
• FROM CLASS)

D_CODE C_NO TITLE ---------- ----------
------------------------------ IFSC
640 Data Mining
50
Group by and Having Clauses

• The syntax of the select statement with group by
  and having clauses
• SELECT ltattribute listsgt
• FROM lttable listsgt
• WHERE ltsearch conditionsgt
• GROUP BY ltattribute listsgt
• HAVING ltconditiongt
• The group by clause is used to form groups of
  rows of a resulting table based on column values.
• The having clause is used to eliminate certain
  groups.
• One line of output is produced for each group

51
Group By

• Q19 For each course offered currently, list the
  number of sessions it has.
• SELECT D_CODE, C_NO, count (CR_no)
• FROM CLASS
• GROUP BY D_CODE, C_NO

D_CODE C_NO COUNT(CR_NO) ----------
- --------- ------------ BUSI
620 1 IFSC 600
1 CS 501
1 CS 545 1 ENGL
200 1 MATH
501 3 BUSI 580
1 IFSC 614
2 MATH 321 2 IFSC
762 2 CS
620 1 CS 702
1 ENGL 400 1 IFSC
799 1
52
Using a HAVING Clause

• The HAVING clause follows the group by is used to
  restrict groups that will be included
• Q20 For each student who got at least 90 in more
  than one course, list his/her SSN and the number
  of courses he/she got at least 90.
• SELECT student_SSN, count()
• FROM transcript
• WHERE grade'A'
• GROUP BY student_SSN
• HAVING count() gt2

STUDENT_SSN COUNT() ----------------------
------------- 711111111 2 811111111
3
53
Using a HAVING Clause

• Q20B List the majors (departments) which have
  at least 2 graduate students.
• select major, count()
• from student
• where statusGrad
• group by major
• having count() gt2

MAJOR COUNT() ----------
---------- IFSC 5 MATH
3
54
Nulls

• Sometimes a condition involves a column that can
  be null
• Q21 Find the SSNs and names of students whose
  majors are null.
• SELECT SSN,S_NAME
• FROM STUDENT
• where major is NULL

SSN S_NAME ----------------
----------------- 111000000 Doe
A Guide to SQL, Seventh Edition
55
A Guide to SQL, Seventh Edition
56
Summary

• Create queries that retrieve data from single
  tables using SELECT commands
• Comparison operators , gt,gt,lt,lt, or ltgt, or !
• Compound conditions using AND,OR, and NOT
• Use the BETWEEN operator
• Use the LIKE operator
• Use the IN operator
• Use and ORDER BY clause to sort data
• Process aggregate functions with COUNT,
  SUM,AVG,MAX, and MIN
• Use the DISTINCT operator and subqueries
• Use GROUP BY, HAVING and IS NULL

57
Exercise 2

• List the students along with their information by
  major.
• For each department, list the D_CODE and the
  number of classes it is offering.
• What is the average grade of the student with
  SSN711111111
• For each student, list his/her SSN, lowest, and
  highest grade he/she got.
• For those students whose average grade is greater
  than 90, list their SSN and average grade.
• List the SSN of instructors who are teaching at
  least 2 classes.
• For each student, list his/her SSN, the number of
  courses that he/she has taken (i.e., that with
  grades given), and the maximal and minimal grade.