Database Sublanguage - Database Design - Lecture Slides, Slides of Database Management Systems (DBMS)

Download Database Sublanguage - Database Design - Lecture Slides and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

SQL (Structured Query Language)

Definition

• SQL- is a database sublanguage for

querying and modifying

relational databases

Language Structure

• SQL is a keyword based language.

• Each statement begins with a unique keyword.

• SQL statements consist of clauses which begin

with a keyword.

• SQL syntax is not case sensitive.

There are 3 basic categories of SQL

Statements:

• SQL-Data Statements
• SQL-Transaction Statements
• SQL-Schema Statements

Syntax

SELECT column_name (using * for all column)

FROM table_name

SELECT column_name

FROM table_name

WHERE condition

Example

LastName FirstName Hanson Olla Stevenson Trove Peterson Kari

“Customer" table

LastName FirstName Address City Hanson Olla 5354 9th^ ST San Jose Stevenson Trove 57 Broadway Las Vegas Peterson Kari 43 Storage Reno

SELECT LastName,FirstName FROM

Customer

Docsity.com

With the WHERE clause, the following operators can be used:

Operator Description

= Equal <> Not equal

Greater than < Less than = Greater than or equal <= Less than or equal

BETWEEN Between an inclusive range LIKE Search for a pattern

Syntax

INSERT INTO table_name

VALUES (value1, value2,....)

You can also specify the columns for which you want to insert data:

INSERT INTO table_name (column1,

column2,...)

VALUES (value1, value2,....)

Example

“Customer" table

INSERT INTO Customer (LastName, City)

VALUES (“Peterson”, ”Reno”)

LastName FirstName Address City Hanson Olla 5354 9th^ ST San Jose Stevenson Trove 57 Broadway Las Vegas Peterson Reno LastName FirstName Address City Hanson Olla 5354 9th^ ST San Jose Stevenson Trove 57 Broadway Las Vegas Docsity.com

Syntax

• UPDATE table_name

SET column_name = new_value WHERE

column_name = some_value

Syntax

• DELETE FROM table_name

WHERE column_name = some_value

You can delete all rows by:

DELETE FROM table_name

DELETE * FROM table_name

Example

“Customer" table

DELETE FROM Customer WHERE LastName =“Stevenson”

LastName FirstName Address City Hanson Olla 5354 9th^ ST San Jose LastName FirstName Address City Hanson Olla 5354 9th^ ST San Jose Stevenson Trove 57 Broadway Las Vegas

SQL-Schema Statements

• CREATE TABLE -- create tables

• DROP TABLE -- drop tables

• GRANT -- grant privileges on tables and views

to other users

• REVOKE-- revoke privileges on tables and

views from other users

Syntax

• To create a database:

CREATE DATABASE database_name

To create a table in a database:

CREATE TABLE table_name ( column_name

data_type, column_name2 data_type, ....... )