sql - v3.ppt

T +44 (0)1344 386000 | E [email protected]

Basics of SQL(Structured Query Language)

Muralidhar, Vasu, VinayService Desk


What is SQL?

• SQL is the standard language for Relation Database System.

• Relation Database System are using different dialects, such as:

MS SQL Server using T-SQL,

Oracle using PL/SQL,

MS Access version of SQL is called JET SQL (native format) etc.


Why SQL?

• Allows users to access data in relational database management systems.

• Allows users to describe the data.

• Allows users to define the data in database and manipulate that data.

• Allows users to create and drop databases and tables.

• Allows users to create view, stored procedure, functions in a database.

• Allows users to set permissions on tables, procedures and views


SQL Commands

Standard SQL commands are CREATE, SELECT, INSERT, UPDATE, DELETE and DROP.

•These commands can be classified into groups based on their nature:

•DDL - Data Definition Language

•DML - Data Manipulation Language

•DCL - Data Control Language

•DQL - Data Query Language


DDL - Data Definition Language

Command Description

CREATE Creates a new table, a view of a table, or other object in database

ALTER Modifies an existing database object, such as a table.

DROP Deletes an entire table, a view of a table or other object in the database.


DML - Data Manipulation Language

Command Description

INSERT Creates a record

UPDATE Modifies records

DELETE Deletes records


DCL - Data Control Language

Command Description

GRANT Gives a privilege to user

REVOKE Takes back privileges granted from user


DQL - Data Query Language

Command Description

SELECT Retrieves certain records from one or more tables


Table

• The table is a collection of related data entries and it consists of columns and rows.

• A table is the most common and simplest form of data storage in a relational database.


Field

• Every table is broken up into smaller entities called fields.

• The fields in the CUSTOMERS table consist of ID, NAME, AGE, ADDRESS and SALARY.


Record or Row

• A record, also called a row of data, is each individual entry that exists in a table.

• A record is a horizontal entity in a table.


Column

• A column is a vertical entity in a table that contains all information associated with a specific field in a table.


NULL value

• A NULL value is a field with no value.

• A NULL value is different than a zero value or a field that contains spaces.


SQL Constraints:

• Constraints are the rules enforced on data columns on table.

• This ensures the accuracy and reliability of the data in the database.

• It maintains the Data Integrity


Types of Constraints:

• NULL & NOT NULL

• PRIMARY KEY

• FOREIGN KEY

• DEFAULT Constraint

• CHECK Constraint

• UNIQUE Constraint


NULL & NOT NULL:

– NULL :- Here Null is not the same as no data, rather, it represents unknown data.

– NOT NULL :- Ensures that a column cannot have NULL value.


• EXAMPLE: Create a new table called CUSTOMERS

• If CUSTOMERS table has already been created


PRIMARY Key:

• A primary key is a field in a table which uniquely identifies each row/record in a database table.

• Primary keys must contain unique values.

• A table can have only one primary key.

• When multiple fields are used as a primary key, they are called a composite key.


• Create Primary Key:

– Creating CUSTOMERS table with Primary Key.

– Create a PRIMARY KEY constraint on the "ID" column when CUSTOMERS table already exists,


• Defining a PRIMARY KEY constraint on multiple columns

– Use the ALTER TABLE statement to add a primary key.


— Create a PRIMARY KEY constraint on the "ID" and "NAMES" columns when CUSTOMERS table already exists.

• Delete Primary Key:


FOREIGN Key:

• A foreign key is a key used to link two tables together. This is sometimes called a referencing key.

• The relationship between 2 tables matches the Primary Key in one of the tables with a Foreign Key in the second table.


• Example:

– CUSTOMERS table: – ORDERS table:


• If ORDERS table has already been created, and the foreign key has not yet been set.

• DROP a FOREIGN KEY Constraint:


DEFAULT Constraint:

• The DEFAULT constraint provides a default value to a column when the INSERT INTO statement does not provide a specific value.

– Example


• CUSTOMERS table has already been created, then to add a DFAULT constraint to SALARY column.

• Drop Default Constraint:


CHECK Constraint:

• The CHECK Constraint enables a condition to check the value being entered into a record. If the condition evaluates to false, the CHECK constraint doesn‘t allows the value entered/record into the table/column.


• CUSTOMERS table has already been created, then to add a CHECK constraint to AGE column.

• Naming the constraint in multiple columns as well.

• DROP a CHECK Constraint:


UNIQUE Constraint:

• The UNIQUE Constraint prevents two records from having identical values in a particular column.


• CUSTOMERS table has already been created, then to add a UNIQUE constraint to AGE column.

• Naming the constraint in multiple columns as well.

• DROP a UNIQUE Constraint:


Understanding Relationship in Database

• There are three types of relationships.

1.One to One

2.One to Many

3.Many to Many


Explanation of Relationships

• One to One : In the One-To-One relation each row in the first table may match only one row in the second and the other way around.

• One to Many : In One-To-Many relation a row in one of the tables can have many matching rows in the second table, but a row the second table can match only one row in the first table.

• Many to Many :In the Many-To-Many relation. many rows from the first table can match many rows in the second and the other way around. To define this type of relation you need a third table whose primary key is composed of the 2 foreign keys from the other 2 table.

sql - v3.ppt

Documents