SQL-Introduction to Computing-Lecture Slides, Slides of Introduction to Computers

Download SQL-Introduction to Computing-Lecture Slides and more Slides Introduction to Computers in PDF only on Docsity!

Structured Query Language

Introduction to SQL

What is SQL?

• When a user wants to get some information

from a database file, he can issue a query.

• A query is a user–request to retrieve data or

information with a certain condition.

• SQL is a query language that allows user to

specify the conditions. (instead of algorithms)

Basic structure of an SQL query General Structure SELECT, ALL / DISTINCT, *, AS, FROM, WHERE Comparison IN, BETWEEN, LIKE "% _" Grouping GROUP BY, HAVING, COUNT( ), SUM( ), AVG( ), MAX( ), MIN( ) Display Order ORDER BY, ASC / DESC Logical Operators AND, OR, NOT Output INTO TABLE / CURSOR TO FILE [ADDITIVE], TO PRINTER, TO SCREEN Union UNION

field type width contents

id numeric 4 student id number

name character 10 name

dob date 8 date of birth

sex character 1 sex: M / F

class character 2 class

hcode character 1 house code: R, Y, B, G

dcode character 3 district code

remission logical 1 fee remission

mtest numeric 2 Math test score

The Situation:

Student Particulars

The Situation:

Student Particulars

General Structure

• The query will select rows from the source tablename

and output the result in table form.

• Expressions expr1 , expr2 can be :
  • (1) a column, or
  • (2) an expression of functions and fields.

SELECT [ALL / DISTINCT] expr1 [AS col1 ], expr2 [AS col2 ] ;

FROM tablename WHERE condition

• And col1 , col2 are their corresponding column names

in the output table.

General Structure

• DISTINCT will eliminate duplication in the output

while ALL will keep all duplicated rows.

• condition can be :
  • (1) an inequality, or
  • (2) a string comparison
  • using logical operators AND, OR, NOT.

SELECT [ALL / DISTINCT] expr1 [AS col1 ], expr2 [AS col2 ] ;

FROM tablename WHERE condition

General Structure

eg. 2 List the names and house code of 1A students.

SELECT name, hcode, class FROM student ; WHERE class="1A" Class 1A 1A 1A 1B 1B :

Class 1A 1A 1A 1B 1B :

class="1A"

General Structure name hcode class Peter R 1A Mary Y 1A Johnny G 1A Luke G 1A Bobby B 1A Aaron R 1A : : :

Result

eg. 2 List the names and house code of 1A students.

General Structure

eg. 5 List the names, id of 1A students with no fee

remission.

SELECT name, id, class FROM student ; WHERE class="1A" AND NOT remission name id class Peter 9801 1A Mary 9802 1A Luke 9810 1A Bobby 9811 1A Aaron 9812 1A Ron 9813 1A Gigi 9824 1A : : :

Result

Comparison expr IN ( value1 , value2 , value3 ) expr BETWEEN value1 AND value expr LIKE "%_"

Comparison

eg. 7 List the students who were not born in January,

March, June, September.

SELECT name, class, dob FROM student ; WHERE MONTH(dob) NOT IN (1,3,6,9) name class dob Wendy 1B 07/09/ Tobe 1B 10/17/ Eric 1C 05/05/ Patty 1C 08/13/ Kevin 1C 11/21/ Bobby 1A 02/16/ Aaron 1A 08/02/ : : :

Result

Comparison

eg. 8 List the 1A students whose Math test score is

between 80 and 90 (incl.)

SELECT name, mtest FROM student ; WHERE class="1A" AND ; mtest BETWEEN 80 AND 90 name mtest Luke 86 Aaron 83 Gigi 84

Result

Comparison

eg. 10 List the Red house members whose names contain

"a" as the 2nd letter.

SELECT name, class, hcode FROM student ; WHERE name LIKE "_a%" AND hcode="R" name class hcode Aaron 1A R Janet 1B R Paula 2A R

Result

Display Order SELECT ...... FROM ...... WHERE ...... GROUP BY ..... ; ORDER BY colname ASC / DESC