Week3 B
Week3 B
Week3 B
SQL I
Pınar Yıldırım
SQL Overview
• Structured Query Language
2
History of SQL
• 1970–E. F. Codd develops relational database concept
• Others followed: INGRES (1981), IDM (1982), DG/SGL (1984), Sybase (1986)
3
Purpose of SQL Standard
• Specify syntax/semantics for data definition and manipulation
4
Benefits of a Standardized Relational
Language
• Reduced training costs
• Productivity
• Application portability
• Application longevity
• 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.
• 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.
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;
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:
Syntax
DROP TABLE table_name;
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:
• 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