Scribd
Upload
42 views44 pages

SQL Final

Uploaded by

Ankit Deora
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPT, PDF, TXT or read online on Scribd
42 views44 pages

SQL Final

Uploaded by

Ankit Deora
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPT, PDF, TXT or read online on Scribd
You are on page 1/ 44

SQL

Structured Query Language

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.
SQL
• Data Definition Language (DDL)
– Create/alter/drop tables and their attributes
• Data Manipulation Language (DML)
– Query one or more tables
– Insert/delete/modify tuples in tables
Table name Attribute names

Tables in SQL
Product

PName Price Category Manufacturer

Gizmo $19.99 Gadgets GizmoWorks

Powergizmo $29.99 Gadgets GizmoWorks

SingleTouch $149.99 Photography Canon

MultiTouch $203.99 Household Hitachi

Tuples or rows
Tables Explained
• The schema of a table is the table name and
its attributes:
Product(PName, Price, Category, Manfacturer)

• A primary key is an attribute whose values


are unique & not null;
we underline a primary key
Product(PName, Price, Category, Manfacturer)
Domain Types in SQL
 char(n). Fixed length character string, with user-specified
length n.
 varchar(n). Variable length character strings, with user-
specified maximum length n.
 int. Integer (a finite subset of the integers that is machine-
dependent).
 smallint. Small integer (a machine-dependent subset of the
integer domain type).
 numeric(p,d). Fixed point number, with user-specified
precision of p digits, with n digits to the right of decimal
point.
 real, double precision. Floating point and double-precision
floating point numbers, with machine-dependent precision.
 float(n). Floating point number, with user-specified precision
of at least n digits.
Tables Explained
• A tuple = a record
– Restriction: all attributes are of atomic type

• A table = a set of tuples


– Like a list…
– …but it is unorderd:
no first(), no next(), no last().
DDL
Commands
Create Table
 An SQL relation is defined using the create table
command:
create table r (A1 D1, A2 D2, ..., An Dn,
(integrity-constraint1),
...,
(integrity-constraintk))
 r is the name of the relation
 each Ai is an attribute name in the schema of
relation r
 Di is the data type of attribute Ai

 Example:
create table branch
(branch_name char(15),
branch_city char(30),
assets integer)
ALTER

 The SQL ALTER


TABLE command is used to
add, delete or modify columns
in an existing table.
 The basic syntax of ALTER
TABLE to add a new column in an
existing table is as follows:
ALTER TABLE table_name ADD
column_name datatype;
 The basic syntax of ALTER TABLE
to DROP COLUMN in an existing
table is as follows:
ALTER TABLE table_name DROP
COLUMN column_name;
 The basic syntax of ALTER
TABLE to change the DATA
TYPE of a column in a table is
as follows:
ALTER TABLE table_name
MODIFY COLUMN column_name
datatype;
DROP

 The SQL DROP TABLE statement


is used to remove a table
definition and all data, indexes,
triggers, constraints, and
permission specifications for that
table.
 Syntax:

Basic syntax of DROP TABLE


statement is as follows:
DROP TABLE table_name;
Constraints
SQL Constraints
• PRIMARY KEY: Primary key is a field that uniquely
identify each record in a database.
• Foreign Key : It is a field (or collection of fields) in one
table that uniquely identifies a row of another table.
• NOT NULL constraint :
– Ensures that column does not accept nulls
• UNIQUE constraint :
– Ensures that all values in column are unique
• DEFAULT constraint :
– Assigns value to attribute when a new row is added to table
• CHECK constraint:
– Validates data when attribute value is entered

15
primary key means attribute having unique and
not null value

Example: Declare branch_name as the primary key


for branch
.
create table branch
(branch_name char(15),
branch_city char(30) not
null,
assets integer,
primary key (branch_name))
Not Null
• The NOT NULL constraint enforces a column
to NOT accept NULL values.
• The NOT NULL constraint enforces a field to
always contain a value. This means that you
cannot insert a new record, or update a record
without adding a value to this field.
• The following SQL enforces the "P_Id"
column and the "LastName" column to not
accept NULL values:
• Example
• CREATE TABLE PersonsNotNull
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
DEFAULT Constraint

• The DEFAULT constraint is used to insert a


default value into a column.
• The default value will be added to all new
records, if no other value is specified.
• The following SQL creates a DEFAULT constraint
on the "City" column when the "Persons" table is
created:
• CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)
FOREIGN KEY Constraint

• A FOREIGN KEY in one table points to a


PRIMARY KEY in another table.
• Let's illustrate the foreign key with an
example. Look at the following two tables:
The "Persons" table:

P_Id LastName FirstName Address City


1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
The "Orders" table:
• Note that the "P_Id" column in the "Orders" table
points to the "P_Id" column in the "Persons" table.
• The "P_Id" column in the "Persons" table is the
PRIMARY KEY in the "Persons" table.
• The "P_Id" column in the "Orders" table is a
FOREIGN KEY in the "Orders" table.
• The FOREIGN KEY constraint also prevents
invalid data from being inserted into the foreign key
column, because it has to be one of the values
contained in the table it points to.
• CREATE TABLE Orders
(
O_Id int PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int REFERENCES Persons(P_Id)
)
UNIQUE Constraint

• The UNIQUE Constraint prevents two


records from having identical values in a
particular column.
• CREATE TABLE CUSTOMERS( ID INT
NOT NULL, NAME VARCHAR (20) NOT
NULL, AGE INT ADDRESS CHAR (25) ,
PHONE INT UNIQUE );
CHECK Constraint

• The CHECK constraint is used to limit the


value range that can be placed in a column.
• If you define a CHECK constraint on a single
column it allows only certain values for this
column.
• If you define a CHECK constraint on a table it
can limit the values in certain columns based
on values in other columns in the row.
• CREATE TABLE Persons
(
P_Id int PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),Age number(2) CHECK
(age>18)
);
DML
COMMANDS
INSERT
• The SQL INSERT INTO Statement is used to add new rows of data
to a table in the database.
• Syntax:
There are two basic syntaxes of INSERT INTO statement as follows:
INSERT INTO TABLE_NAME (column1, column2,
column3,...columnN)] VALUES (value1, value2, value3,...valueN);
Here, column1, column2,...columnN are the names of the columns in
the table into which you want to insert data.
• You may not need to specify the column(s) name in the SQL query if
you are adding values for all the columns of the table. But make sure
the order of the values is in the same order as the columns in the table.
The SQL INSERT INTO syntax would be as follows:
• INSERT INTO TABLE_NAME VALUES
(value1,value2,value3,...valueN);
• Assume we wish to insert a new row in the
"Customers" table. We can use the following
SQL statement:
• INSERT INTO Customers (CustomerName,
ContactName, Address, City, PostalCode,
Country)
VALUES ('Cardinal','Tom B. Erichsen','Skagen
21','Stavanger','4006','Norway');
UPDATE
• The SQL UPDATE Query is used to modify the existing
records in a table.
• You can use WHERE clause with UPDATE query to
update selected rows otherwise all the rows would be
affected.
• The basic syntax of UPDATE query with WHERE
clause is as follows:
• UPDATE table_name SET column1 = value1, column2
= value2...., columnN = valueN WHERE [condition];
• UPDATE CUSTOMERS SET ADDRESS = 'Pune'
WHERE ID = 6;
DELETE
• The SQL DELETE Query is used to delete the
existing records from a table.
• You can use WHERE clause with DELETE query to
delete selected rows, otherwise all the records
would be deleted.
• The basic syntax of DELETE query with WHERE
clause is as follows:
• DELETE FROM table_name WHERE [condition];
• DELETE FROM CUSTOMERS WHERE ID = 6;
The SQL SELECT Statement

• The SELECT statement is used to select data from a


database.
• SQL SELECT Syntax
SELECT column_name,column_name
FROM table_name;
• SELECT * FROM table_name;
• The following SQL statement selects the
"CustomerName" and "City" columns from the
"Customers" table:
• Example
• SELECT CustomerName,City FROM Customers;
Simple SQL Query
Product PName Price Category Manufacturer
Gizmo $19.99 Gadgets GizmoWorks
Powergizmo $29.99 Gadgets GizmoWorks
SingleTouch $149.99 Photography Canon
MultiTouch $203.99 Household Hitachi

SELECT
SELECT **
FROM
FROM Product
Product
WHERE
WHERE category=‘Gadgets’
category=‘Gadgets’
PName Price Category Manufacturer
Gizmo $19.99 Gadgets GizmoWorks
Powergizmo $29.99 Gadgets GizmoWorks
“selection”
Product PName Price Category Manufacturer
Gizmo $19.99 Gadgets GizmoWorks
Powergizmo $29.99 Gadgets GizmoWorks
SingleTouch $149.99 Photography Canon
MultiTouch $203.99 Household Hitachi

SELECT
SELECT PName,
PName,Price,
Price,Manufacturer
Manufacturer
FROM
FROM Product
Product
WHERE
WHERE Price
Price>>100
100
PName Price Manufacturer
“selection” and SingleTouch $149.99 Canon
“projection” MultiTouch $203.99 Hitachi
Eliminating Duplicates
Category
SELECT
SELECT DISTINCT
DISTINCTcategory
category Gadgets
FROM
FROM Product
Product Photography
Household

Compare to:
Category
Gadgets
SELECT
SELECT category
category Gadgets
FROM
FROM Product
Product Photography
Household
The SQL LIKE Operator

• The LIKE operator is used to search for a


specified pattern in a column.
• SQL LIKE Syntax
• SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;
• SELECT * FROM Customers
• WHERE City LIKE 's%';
WHERE clause
• This clause is used to specify a condition while
fetching the data from single table or joining
with multiple tables.
• If the given condition is satisfied then only it
returns specific value from the table. You
would use WHERE clause to filter the records
and fetching only necessary records.
• The WHERE clause is not only used in
SELECT statement, but it is also used in
UPDATE, DELETE statement, etc.,
• SELECT ID, NAME, SALARY FROM
CUSTOMERS WHERE SALARY > 2000;
• This would produce the following result:
VIEW
• In SQL, a view is a virtual table based on the result-set of an
SQL statement.
• A view contains rows and columns, just like a real table. The
fields in a view are fields from one or more real tables in the
database.
• You can add SQL functions, WHERE, and JOIN statements to a
view and present the data as if the data were coming from one
single table.
• Syntax:
• CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
• CREATE VIEW CurrentProductList AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No

Common questions

Powered by AI

In relational databases, a PRIMARY KEY constraint uniquely identifies each record within a table, ensuring that the values in this column are unique and not null . Conversely, a FOREIGN KEY constraint establishes a relationship between two tables by pointing from a column in one table (the child table) to a column in another table (the parent table), which must be a PRIMARY KEY. This relationship not only ensures data integrity across tables but also prevents invalid data by restricting the values in the child table’s Foreign Key column to ones existing in the parent table .

The SQL CHECK constraint ensures data integrity by specifying a range of permissible values for a column. It checks data entries against defined conditions, maintaining consistency and validity. For instance, defining a CHECK constraint on the 'age' column to accept only values greater than 18 prevents invalid data entry . By enforcing these rules, CHECK constraints help maintain the accuracy and reliability of the database.

The SQL WHERE clause is used to specify conditions for filtering records in a query. It enhances database queries by returning only the data that meets specified conditions, thus improving efficiency and relevance of the query results. This clause not only applies to SELECT statements but is also utilized in UPDATE and DELETE operations to modify or remove only those records that satisfy the given criteria .

The SQL UNIQUE constraint ensures all values in a column are distinct, thus eliminating duplicate entries and enhancing data integrity . However, it also restricts user flexibility, as any duplication in column values leads to constraint violations, potentially complicating data entry and requiring additional data checks and validation steps during data manipulation and loading.

SQL views act as virtual tables that derive their data from SQL query results. They improve database management by simplifying complex queries, ensuring data abstraction, and providing a level of security by limiting user access to specific data without revealing underlying table structures. Views can also encapsulate SQL statements, allowing for easy reuse and modification without altering the base tables .

Using 'SELECT *' retrieves all columns from the queried tables, which can be inefficient with large datasets as it returns more data than necessary. In contrast, specifying column names in a SELECT statement retrieves only the desired columns, optimizing data transfer and processing by focusing on relevant data fields .

The SQL DROP TABLE command removes a complete table, including its definition, data, indexes, triggers, constraints, and permission specifications. This effectively deletes the table from the database. For example, executing DROP TABLE Customers; would eliminate the 'Customers' table and all its data from the database .

DEFAULT constraints in SQL ensure that a column has a predefined value when no specific value is provided during record insertion. This increases database consistency by reducing the incidence of NULL values and providing a standard default value, enhancing data predictability and standardization within the database .

The SQL ALTER TABLE command modifies the structure of an existing table. It can be used to add, delete, or modify columns. For instance, to add a new column, the syntax is: ALTER TABLE table_name ADD column_name datatype; To drop a column, it is: ALTER TABLE table_name DROP COLUMN column_name; To change a column's data type, the syntax is: ALTER TABLE table_name MODIFY COLUMN column_name datatype .

The SQL LIKE operator facilitates pattern matching by allowing queries to search for patterns within string data, using wildcards like '%' for multiple characters or '_' for a single character. This capability is particularly useful for performing search tasks in textual data, identifying entries that match specific string patterns, greatly enhancing query flexibility and detail .

You might also like