Week3 B

Download as pdf or txt
Download as pdf or txt
You are on page 1of 22

Database Management Systems

SQL I

Pınar Yıldırım
SQL Overview
• Structured Query Language

• The standard for relational database management systems (RDBMS)

• RDBMS: A database management system that manages data as a


collection of tables in which all relationships are represented by
common values in related tables

2
History of SQL
• 1970–E. F. Codd develops relational database concept

• 1974-1979–System R with Sequel (later SQL) created at IBM Research Lab

• 1979–Oracle markets first relational DB with SQL

• 1981 – SQL/DS first available RDBMS system on DOS/VSE

• Others followed: INGRES (1981), IDM (1982), DG/SGL (1984), Sybase (1986)

• 1986–ANSI SQL standard released

• 1989, 1992, 1999, 2003, 2006, 2008–Major ANSI standard updates

• Current–SQL is supported by most major database vendors

3
Purpose of SQL Standard
• Specify syntax/semantics for data definition and manipulation

• Define data structures and basic operations

• Enable portability of database definition and application modules

• Allow for later growth/enhancement to standard (referential integrity,


transaction management, user-defined functions, extended join
operations, national character sets)

4
Benefits of a Standardized Relational
Language
• Reduced training costs

• Productivity

• Application portability

• Application longevity

• Reduced dependence on a single vendor

• Cross-system communication

5
SQL Environment

• Catalog
– A set of schemas that constitute the description of a database
• Schema
– The structure that contains descriptions of objects created by a user (base
tables, views, constraints)
• Data Definition Language (DDL)
– Commands that define a database, including creating, altering, and
dropping tables and establishing constraints
• Data Manipulation Language (DML)
– Commands that maintain and query a database
• Data Control Language (DCL)
– Commands that control a database, including administering privileges and
committing data

6
SQL
• SQL is a standard language for accessing and manipulating databases.

• SQL stands for Structured Query Language

• SQL lets you access and manipulate databases

• SQL became a standard of the American National Standards Institute


(ANSI) in 1986, and of the International Organization for Standardization
(ISO) in 1987
What Can SQL do?
• SQL can execute queries against a database
• SQL can retrieve data from a database
• SQL can insert records in a database
• SQL can update records in a database
• SQL can delete records from a database
• SQL can create new databases
• SQL can create new tables in a database
• SQL can create stored procedures in a database
• SQL can create views in a database
• SQL can set permissions on tables, procedures, and views
SQL
• Although SQL is a standard,but there are different versions of the SQL
language.

• However, to be compliant with the standard, all versions support main


commands (such as SELECT, UPDATE, DELETE, INSERT, WHERE).

• Most of the SQL database softwares also have their own features in
addition to the SQL standard.
RDBMS
• RDBMS means Relational Database Management System.

• Some RDBMS: MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft
Access.

• The data in RDBMS is stored in database objects called tables. A table is a


collection of related data entries and it consists of columns and rows.
The SQL CREATE DATABASE Statement
The CREATE DATABASE statement is used to create a new SQL database.

Syntax
CREATE DATABASE databasename;
The following SQL statement creates a database called "SchoolDB":

Example
CREATE DATABASE SchoolDB;
The SQL DROP DATABASE Statement
The DROP DATABASE statement is used to drop an existing SQL database.

Syntax
DROP DATABASE databasename;

Warning: Deleting a database can delete complete information stored in the


database!
DROP DATABASE Example
The following SQL statement drops the existing database "SchoolDB":

Example
DROP DATABASE SchoolDB;
The SQL CREATE TABLE Statement
• The CREATE TABLE statement is used to create a new table in a database.
Syntax
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
....
);
• The column parameters are the names of the columns of the table.
• The datatype parameter is the type of data the column can hold (e.g. varchar,
integer etc.).
SQL CREATE TABLE Example
• The following example creates a table called " Employee" that contains
five columns: E_ID, LastName, FirstName, Address, and City:

Example
CREATE TABLE Employee (
E_ID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
SQL CREATE TABLE Example
• The empty " Employee" table will now look like this:

E_ID LastName FirstName Address City


The SQL DROP TABLE Statement
• The DROP TABLE statement is used to drop an existing table in a database.

Syntax
DROP TABLE table_name;

• Warning: Deleting a table may delete complete information stored in the


table
• The following SQL statement drops the existing table "Student":

Example
DROP TABLE Student;
SQL TRUNCATE TABLE
• The TRUNCATE TABLE statement is used to delete the data inside a table,
but not the table itself.

Syntax
TRUNCATE TABLE table_name;
SQL ALTER TABLE Statement
• The ALTER TABLE statement is used to add, delete, or modify columns in
an existing table.

• The ALTER TABLE statement is also used to add and drop various
constraints on an existing table.
ALTER TABLE - ADD Column
• To add a column in a table, use the following syntax:

ALTER TABLE table_name


ADD column_name datatype;

• The following SQL adds an "Email" column to the " Student" table:
ALTER TABLE Student
ADD Email varchar(255);
ALTER TABLE - DROP COLUMN
• To delete a column in a table, use the following syntax
ALTER TABLE table_name
DROP COLUMN column_name;

• The following SQL deletes the "Email" column from the " Student" table:
Example
ALTER TABLE Student
DROP COLUMN Email;
References
• Modern Database Management 11th Edition, Jeffrey A. Hoffer, V. Ramesh,
Heikki Topi © 2013 Pearson Education, Inc. Publishing as Prentice Hall

You might also like