STRUCTURED
This presentation is the property of its rightful owner.
Sponsored Links
1 / 50

STRUCTURED QUERY LANGUAGE PowerPoint PPT Presentation


  • 75 Views
  • Uploaded on
  • Presentation posted in: General

STRUCTURED QUERY LANGUAGE. Chandra S. Amaravadi. IN THIS PRESENTATION. Codd’s rules for relational systems Types of SQL DDL and DML Examples. CODD’S RULES AND TYPES OF SQL. CODD’S RULES FOR RDBMSs. Codd has written a paper in which he outlined the rules for

Download Presentation

STRUCTURED QUERY LANGUAGE

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Structured query language

STRUCTURED

QUERY LANGUAGE

Chandra S. Amaravadi


Structured query language

IN THIS PRESENTATION

  • Codd’s rules for relational systems

  • Types of SQL

  • DDL and DML

  • Examples


Structured query language

CODD’S RULES

AND

TYPES OF SQL


Structured query language

CODD’S RULES FOR RDBMSs

Codd has written a paper in which he outlined the rules for

relational systems. These are as follows:

  • Information representation

  • Guaranteed access

  • Dynamic on-line catalog

  • Comprehensive data sub-language

  • View updating

Note: Codd was a research fellow at IBM in the ’70s


Structured query language

CODD’S RULES FOR RDBMSs..

  • High-level insert, update and delete

  • Physical data independence

  • Logical data independence

  • Integrity independence

  • Distribution independence

  • Non-subversion


Structured query language

CODD’S RULES FOR RDBMSs

  • Information representation -- all information should be represented as atomic values

    in tables.

  • Guaranteed access -- given a row, column and table name we should be able to

    access values in the table.

  • Online catalog -- the system catalog (data dictionary) should be online

    and accessible by the system.

  • Comp. data lang. -- there should be a language for data definition and data

    manipulation.

  • View updating -- the system must be able to automatically update views

    based on a base table.


Structured query language

CODD’S RULES FOR RDBMSs

  • High level insertion, update.. -- insertion, deletion and update should operate on a

    table.

  • Physical data independence -- should be able to change internal storage structures

    without affecting application programs.

  • Logical data independence -- should be able to change logical scheama

    without affecting application programs.

  • Integrity independence -- integrity controls must be independent of appln. prog.

  • Distribution independence -- the users/appln. programs should not be affected

    by where the data is physically stored.

  • Non subversion -- should not be able to bypass integrity rules by

    using the data sub language.


Structured query language

STRUCTURED QUERY LANGUAGE

Structured Query Language originated

from SEQEL (early 1980’s). SQL (is/was):

  • based on relational calculus

    (uses select, project, join etc.)

  • standardized in ’82,’95, ‘05

  • Embedded SQL a standard in ‘89

  • Two major types and three other types

    • DDL, DML

    • SQL/T, SQL/I, SQL/AU (DCL)

    • Embedded SQL


Structured query language

STRUCTURED QUERY LANGUAGE

There are five types of SQL as follows:

DDL- Creating / modifying tables,

views and indexes

DML - Retrieving / inserting / updating information

SQL/T - Transaction boundaries

SQL/I - Integrity constraints

SQL/AU. - Authorization (also called DCL)


Structured query language

DATA DEFINITION

LANGUAGE


Structured query language

DATA DEFINITION LANGUAGE

DDL is the language used to define/modify the database

Schema.

  • Create/Open Database (not discussed)Create Database Employee;

  • Create/Alter/Drop Table

  • Create/Drop View

  • Create/Drop Index


Structured query language

DATA DEFINITION LANGUAGE..

TYPICAL FORMAT

Action DatabaseComponent ComponentName…

Create

Drop

Table

View

Index

Cust.

Emp.

…………

Alter

Table

Emp.


Structured query language

DATA DEFINITION LANGUAGE..

Command used to create tables

CREATE TABLE Table name (attribute attr. type,

attribute attr.type..)

[CONSTRAINT Constr name TYPE attr];

Command used to change attributes in tables

ALTER TABLE Table name ADD attr. attr. type,

attr. attr. type;

Command used to delete table definitions

DROP TABLE Table name ;


Structured query language

CREATE TABLE

Creates a table (schema)

Dept.

d_no d_name d_mgr_ssn d_phone

CREATE TABLE DEPT. ( d_no Integer,

d_name VarChar(15),

d_mgr_SSN Char(9),

d_phone Char(12));


Structured query language

ALTER, DROP TABLE

Alter table adds/drops attributes; Drop table drops the entire table.

Dept.

d_no d_name d_mgr_ssn d_phone no_of_emp

ALTER TABLE DEPT. ADD no_of_emp Integer;

ALTER TABLE DEPT. DROP no_of_emp;

DROP TABLE Dept.;


Structured query language

CREATE VIEW

Command used to create VIEWS

CREATE VIEW Viewname AS SELECT...

  • A view is the way a user looks at the data.

  • Views are subsets of the data in the database.

  • A view could include data from more than one table.

  • All application programs access data via views.

  • Views provide logical data independence.

  • Reports can be created from views (as well as from tables)


Structured query language

CREATE VIEW..

Creates a query; AS is an “alias” or name

Emp.

e_ssn e_name e_title

Create a view of employees who are analysts

CREATE VIEW Analysts

AS

SELECT e_name, e_title

FROM Emp.

WHERE e_title = “analyst”;


Structured query language

MORE DDL COMMANDS..

Creates a view

Called analysts

CREATE VIEW ANALYSTS.....

DROP VIEW ANALYSTS;

Drops the view

CREATE INDEX DEPT_INDEX

ON DEPT(d_no);

Creates an index

called Dept_index

on attribute d_no

DROP INDEX DEPT_INDEX;

Drops the index


Structured query language

DATA MANIPULATION

LANGUAGE


Structured query language

DATA MANIPULATION LANGUAGE

DML is the language used to create/modify and delete

data in the database.

  • INSERT

    • insert a record

  • UPDATE

    • change values

  • DELETE

    • delete records

  • SELECT

    • choose record


Structured query language

THE INSERT COMMAND..

Inserts a record into a table with name TABLENAME.

FORMAT

INSERT INTO TABLENAME

VALUES (attr1 value, attr2 value….);


Structured query language

THE INSERT COMMAND..

Insert employee record

EMP.

e_ssn e_name e_title

INSERT INTO EMP

VALUES (‘978-98-9878’, ‘Sullivan’, ‘developer’);


Structured query language

THE UPDATE COMMAND

Update employee title to ‘analyst’

EMP.

e_ssn e_name e_title

UPDATE EMP

SET e_title = ‘analyst’

WHERE e_name = ‘Sullivan’;

/* table name */

/* new values */

/* condition */

EMP.


Structured query language

THE DELETE COMMAND

Delete Employee record

EMP.

DELETE FROM EMP.

WHERE e_name = ‘Sullivan’;

EMP.

What if you were to issue,

DELETE FROM EMP?


Structured query language

THE SELECT STATEMENT


Structured query language

THE SELECT STATEMENT

SELECT <Attribute list>

FROM <Relations>

[WHERE <Conditions> AND/OR <Conditions>]

[GROUP BY <Attribute list>]

[HAVING <Conditions>]

[ORDER BY <Attribute list>

DESC/ASC];

Group by is used to organize data into groups and provide summary

information. Having is used for the group condition.


Structured query language

ADDITIONAL NOTES ON SELECT

Notes on the select statement

  • The Select part can include literals (“The number of..”)

  • Some functions can be included in SELECT part or WHERE part

  • More than one table and one condition can be specified

  • Conditions are connected by logical operators -- and/or etc.

  • When GROUP BY is used, the WHERE clause is not used.

  • Instead the group condition is specified by HAVING.

  • ORDER BY is optional and used if sorting is required.


Structured query language

DISCUSSION

The output of a SELECT statement is:

a) an attribute ?

b) a single record ?

c) table ?


Structured query language

FUNCTIONS IN SQL

Operators to carry out different types of calculations

  • Logical

  • Arithmetic

  • String

  • Date

IN WHICH PART OF THE QUERY ARE FUNCTIONS USED?


Structured query language

LOGICAL OPERATORS

Logical operators are generally used to carry out comparison

  • “=“, “>“, “<“

  • “>=“, “<=“

  • “<>“ OR “!=“

  • (NOT) BETWEEN X1 AND X2 (inclusive)

  • LIKE

  • “_” or “%”

  • IN

  • (NOT) NULL

Dept.

(Select….Where D_Name like “%fin%”)

Finance

Financial Records


Structured query language

LOGICAL OPERATORS..

To select employees between certain income range

Select emp#, emp name

From emp

Where income between 70000 and 90000;

To select customers in Macomb, Chicago or Bloomington

Cust.

Select cust.cust#

From cust

Where cust.zip in (61455, 60601….)


Structured query language

BUILT-IN ARITHMETIC FUNCTIONS

Arithmetic functions are used to carry out math operations

  • ABS

  • ROUND

  • TRUNC

  • COUNT

  • SUM

  • AVG

  • MAX

  • MIN

Select count(emp_name)…

Select max(emp_salary)…..


Structured query language

STRING FUNCTIONS

String functions are used to carry out string manipulation

  • LENGTH(string)

  • SUBSTR(string, start, no of ch.)

  • LOWER

  • UPPER

Select substr(prob_descr, 0, 10)

………

Prob_descr = “I am unable to log in…”


Structured query language

DATE FUNCTIONS

Date functions are used to carry out date arithmetic

  • ADD_MONTHS(1/1/13, 5) = 6/1/13

  • MONTHS_BETWEEN (sysdate, hiredate)

  • NEXT_DAY(hiredate, ‘Friday’)

  • TO_DATE(string, picture)

TO_DATE(“12/09/13”, ‘DY th MM, YYYY’) = 9 th DEC, 2013

Next_day(2/20/13, ‘Friday’) = 2/25/13


Structured query language

EMP.

e_ssn e_name e_title

SIMPLE RETRIEVALS

Select employees who are analysts

SELECT e_name

FROM EMP

WHERE e_title = ‘analyst’;


Structured query language

SIMPLE RETRIEVALS..

EMPLOYEE


Structured query language

EMP.

e_ssn e_name e_title

SIMPLE RETRIEVALS..

Select employees whose name ends with ‘son’

SELECT e_name

FROM EMP

WHERE e_name like ‘%son’


Structured query language

EMP.

e_ssn e_name e_title

SIMPLE RETRIEVALS..

Select employees whose name does not begin with ‘a’

SELECT e_name

FROM EMP

WHERE e_name not like ‘a%’


Structured query language

EMP.

e_ssn e_name e_title

RETRIEVALS WITH AGGREGATES..

Count the number of developers

SELECT COUNT(e_name)

FROM EMP

WHERE e_title = ‘developer’


Structured query language

RETRIEVALS WITH AGGREGATES..

EMP.

e_ssn e_name e_title e_salary

SELECT “Average Salary=“, Avg(e_salary)

From EMP

Where e_title = ‘developer’ or

e_title = ‘analyst’ ;

What does this query do?


Structured query language

RETRIEVALS WITH EXPRESSIONS..

List the employees and their witholdings (calculated as

8% of salary).

EMP.

e_ssn e_name e_title e_salary

SELECT e_name, e_salary * 0.08 AS Withholdings

FROM EMP


Structured query language

EMP.

e_ssn e_name e_title

RETRIEVALS WITH GROUP BY..

List all job titles and the number of emps in each

SELECT e_title, COUNT(e_name)

FROM EMP

GROUP BY e_title


Structured query language

GROUP BY..

EMPLOYEE


Structured query language

RETRIEVALS WITH HAVING..

List the number of employees in each job category with

salary > $50,000

EMP.

e_ssn e_name e_title e_salary

SELECT “Number of: “, e_title, “=“, COUNT(e_name)

FROM EMP

GROUP BY e_title

HAVING e_salary > 50000


Structured query language

RETRIEVALS WITH HAVING..

EMP.


Structured query language

RETRIEVAL FROM MULTIPLE TABLES

  • Sharing of information a key concept

  • Normalization process leads to multiple tables

  • When data is retrieved from > 1 table, need to

  • link tables together

  • This is done by equating FK values with PK values

  • for each set of tables that need to be linked

Emp.

emp#, emp name, dept#

Dept.

dept#, dept name, mgr


Structured query language

Dept.

d_named_nod_mgr_ssnd_phone

RETRIEVAL FROM MULTIPLE TABLES

EMP.

e_ssn e_name e_title

SELECT Emp.e_name, Dept.d_name

FROM EMP, dept.

WHERE EMP.e_ssn = dept.d_mgr_ssn


Structured query language

RETRIEVAL FROM MULTIPLE TABLES..

EMPLOYEE

DEPARTMENT


Structured query language

DISCUSSION

Write SQL queries for the following:

1. Create Emp table with E_SSN, E_Name, E_title as attr. -- assume

data types.

2. Add E_salary to the Employee table.

3. Create an index, “Ti_ndx” on E_title.

4. List employees and job titles in order of title.

5. List employees other than developers.

6. Create a view “hi_flier” listing developers with salary > $100K.

7. Count the #. of employees who are managers.


  • Login