module 2

Module Content

• An Introduction to Database Designs

• An Introduction to Tables

• An Introduction to SQL Statements

• An Introduction to DDL Statements

What is SQL ?

SQL (Structured Query Language) is a programming language used to manage and manipulate data stored in relational databases. It is used to create, modify, and query databases. SQL is a standard language for managing data held in relational database management systems (RDBMS), or for stream processing in a relational data stream management system (RDSMS). Some common tasks that can be performed using SQL include creating new databases and tables, inserting data into tables, updating data in tables, and querying data from tables.

As a reminder, here is a link: introduction

And here is a wikipedia page: wiki

what is DDL ?

In SQL, DDL stands for Data Definition Language. It is a type of SQL statement that is used to define the database schema. It simply deals with descriptions of the database schema and is used to create and modify the structure of database objects in the database. Examples of DDL statements include CREATE, ALTER, and DROP.

Here are some examples of DDL statements:

• CREATE TABLE is used to create a new table in the database.

• ALTER TABLE is used to modify the structure of an existing table.

• DROP TABLE is used to delete a table from the database.

• TRUNCATE TABLE is used to delete all the data from a table, but the table structure and its schema remain unchanged.

• CREATE INDEX is used to create an index (search key) on a table.

These statements are used to define the database structure, but they do not modify the data stored in the table.

Relational Database Management Systems (RDBMS)

Tables

Relational Database Management Systems (RDBMS)

Tables

• The RDBMS database uses tables to store data. A table is a collection of related data entries and contains rows (records) and columns (fields) to store data.

NULL values

Relational Database Management Systems (RDBMS)

NULL Values

• A NULL value in a table is a value in a field that appears to be blank, which means a field with a NULL value is a field with no value.

• It is very important to understand that a NULL value is different than a zero value or a field that contains spaces. A field with a NULL value is the one that has been left blank during a record creation.

SQL

Relational Database Management Systems (RDBMS)

Structured Query Language (SQL)

• SQL is a programming language for Relational Databases. SQL comes as a package with all major distributions of RDBMS.

• SQL comprises both data definition and data manipulation languages. Using the data definition properties of SQL, one can design and modify database schema, whereas data manipulation properties allows SQL to store and retrieve data from Database.

Relational Database Management Systems (RDBMS)

Structured Query Language (SQL)

• Some database systems require a semicolon at the end of each SQL statement.

• A Semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server.

Relational Database Management Systems (RDBMS)

Advantages of Structured Query Language (SQL)

• 1. No programming needed

• 2. High-Speed Query Processing

• 3. Standardized Language

• 4. Portability

• 5. Interactive language

• 6. More than one Data View

DDL

Relational Database Management Systems (RDBMS)

DDL

• DDL stands for data definition language.

• DDL Commands deal with the schema, i.e., the table in which our data is stored.

All the structural changes such as creation, deletion and alteration on the table can be carried with the DDL commands in SQL.

Commands covered under DDL are:

• CREATE

• ALTER

• DROP

• TRUNCATE

• RENAME

The create statement

Relational Database Management Systems (RDBMS)

The CREATE Statement

• In SQL, whenever we wish to create a new database or a table in a database, we use CREATE command.

Syntax to create a new database:

CREATE DATABASE DatabaseName;  

Code Example:

CREATE DATABASE database1;

The ALTER statement

Relational Database Management Systems (RDBMS)

The ALTER Statement

• In SQL, whenever we wish to alter the table structure, we will use the ALTER command.

• Syntax of ALTER command to add a new column:

  • ALTER TABLE table_name ADD column_name;

  • ALTER TABLE table_name DROP COLUMN column_name;  

  • ALTER TABLE table_name RENAME COLUMN old_column_

  • name TO  new_column_name;

The DROP statement

Relational Database Management Systems (RDBMS)

The DROP Statement

• The DROP command is used to remove or delete the table's records and the table's structure from the database.

Syntax :

DROP TABLE  table_name;  

Code Example:

Write a query to delete the school table from the SCHOOL database.

DROP TABLE  school;  

The TRUNCATE statement

Relational Database Management Systems (RDBMS)

The TRUNCATE Statement

• A TRUNCATE command is used to delete the table's records, but the table's structure will remain unaffected in the database.

Syntax:

TRUNCATE TABLE table_name;  

Code Example:

 TRUNCATE  TABLE  t_school;  

The RENAME Statement

Relational Database Management Systems (RDBMS)

The RENAME Statement

• Rename COMMAND is used to give a new name to an existing table.

Syntax to rename a table:

RENAME TABLE old_table_name TO new_table_name;   

Code Example:

RENAME TABLE  student TO top_student; 

Here are the module 2 notes