QUERY FORMULATION WITH SQL, Slides of Database Management Systems (DBMS)

Download QUERY FORMULATION WITH SQL and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Database Systems

Lecture # Topic: Query Formulation with SQL Instructor: Lecturer Ayesha Naseer

Scope of SQL

• SQL was designed as a language for database definition, manipulation, and control.

Getting Started with SELECT Statement

• The SELECT statement supports data retrieval from one or more tables.
• The SELECT statement described here has the following format:

Table Creation

Create Table Student ( StdSSN VarChar(30) Primary Key, StdFirstName Char(15), StdLastName Char(15), StdCity Char(20), StdState Char(6), StdZip VarChar(30), StdMajor Char(15), StdClass Char(6), StdGPA Float );

INSERT Statement

Insert Into <Table-name> Values (column 1 value, column 2 value …) Insert Into Student Values (‘123- 45 - 6789’, ‘HOMER’, ‘WELLS’, ‘SEATTLE’, ‘WA’, ‘98121-1111’, ’IS’, ‘FR’, 3.00)

Show specific columns

• List name, city and grade of all students. Select StdFisrtName, StdLastName, StdCity, StdCGPA From Student

Distinct Rows

Std Student StudentStudent Std^ Std^ Std Std (^) Std (^) Std Std Std Student^ Student Student

Expression in SELECT and WHERE

‘31-Dec-1996’

LIKE operator in WHERE Clause

Testing for NULLS

Complex Logical Condition In WHERE CLAUSE

ORDER BY with two Columns

Group by Clause

• The GROUP BY and HAVING clauses are used to show results about groups of rows rather than individual rows.
• GROUP BY reminder The columns in the SELECT clause must either be in the GROUP BY clause or be part of a summary calculation with an aggregate function.