SQL queries. DDL (Data Definition Language)


11.4.2.2 compare the data definition language (DDL), and the data manipulation language (DML) 
11.4.2.3 describe the basic SQL queries for working with tables in a database: CREATE, ALTER and DROP

SQL queries. Data Definition Language.

What can we do with SQL? 

  • Create new databases
  • Create new tables
  • Change the structure of database
  • Get data from databases
  • Insert records to database tables 
  • Update records of database tables 
  • Delete records in database tables 

SQL (Structured Query Language) is a standard language for accessing and manipulating databases.

Data Definition Language (DDL) is a family of computer languages ​​used in computer programs to describe the structure of databases. 

The functions of DDL operators are defined by the first word in a sentence.

DDL is used to create the database strucrure; that is, to define which attributes belong in which tables. It also allows you to cretae users and grant access rights to users.

The SQL CREATE DATABASE Statement

Syntax Example
CREATE DATABASE databasename; CREATE DATABASE projectDB;

SQL DROP DATABASE Statement

Syntax Example
DROP DATABASE databasename; DROP DATABASE projectDB;

SQL CREATE TABLE Statement

Syntax Example
CREATE TABLE table_name 
(
    column1 datatype,
    column2 datatype,
    column3 datatype,
    ....
);
CREATE TABLE users 
(
    userID int,
    surname varchar(25),
    firstname varchar(25),
    address varchar(50),
    iin varchar(12) 
);

The PRIMARY KEY constraint uniquely identifies each record in a table.
Primary keys must contain UNIQUE values, and cannot contain NULL values.
A table can have only one primary key, which may consist of single or multiple fields.

CREATE TABLE users
(
    userID int NOT NULL,
    surname varchar(25),
    firstname varchar(25),
    address varchar(50),
    iin varchar(12),
    PRIMARY KEY (userID)
);

Data dictionary

SQL ALTER TABLE Statement

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

Syntax Example
ALTER TABLE table_name
ADD column_name datatype;
ALTER TABLE users
ADD login varchar(25);
ALTER TABLE table_name
DROP COLUMN column_name;
ALTER TABLE users
DROP COLUMN address;

 


Questions:


Exercises:

Ex. 1

Test (Ex. 1-7) on w3schools.com


Exam questions:


 

Категория: Relational databases | Добавил: bzfar77 (10.11.2020)
Просмотров: 163 | Теги: query, DDL, DML, SQL | Рейтинг: 0.0/0
Всего комментариев: 0
avatar