Introduction to structured query language (sql) (1)

Created by: Mrs. Rajni
Asst. Prof. in CS
Pt .Mohan Lal SD College for women
,Gurdaspur

 Explore basic commands and functions of
SQL
 How to use SQL for data administration (to
create tables, indexes, and views)
 How to use SQL for data manipulation (to
add, modify, delete, and retrieve data)
 How to use SQL to query a database to
extract useful information
2

 SQL functions fit into two broad categories:
 Data definition language
 SQL includes commands to:
 Create database objects, such as tables, indexes,
and views
 Define access rights to those database objects
 Data manipulation language
 Includes commands to insert, update, delete, and
retrieve data within database tables
3

 SQL is relatively easy to learn
 Basic command set has vocabulary of less
than 100 words
 Nonprocedural language
 American National Standards Institute (ANSI)
prescribes a standard SQL
 Several SQL dialects exist
4

 Examine simple database model and
database tables that will form basis for many
SQL examples
 Understand data environment
8

 Data type selection is usually dictated by
nature of data and by intended use
 Pay close attention to expected use of
attributes for sorting and data retrieval
purposes
9

 Use one line per column (attribute)
definition
 Use spaces to line up attribute
characteristics and constraints
 Table and attribute names are capitalized
 NOT NULL specification
 UNIQUE specification
11

 Primary key attributes contain both a NOT
NULL and a UNIQUE specification
 RDBMS will automatically enforce referential
integrity for foreign keys
 Command sequence ends with semicolon
12

 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
13

 Adding table rows
 Saving table changes
 Listing table rows
 Updating table rows
 Restoring table contents
 Deleting table rows
 Inserting table rows with a select subquery
14

 INSERT
 Used to enter data into table
 Syntax:
 INSERT INTO columnname
VALUES (value1, value2, … , valuen);
15

When entering values, notice that:
 Row contents are entered between
parentheses
 Character and date values are entered
between apostrophes
 Numerical entries are not enclosed in
apostrophes
 Attribute entries are separated by commas
 A value is required for each column
Use NULL for unknown values
16

 Changes made to table contents are not
physically saved on disk until, one of the
following occurs:
 Database is closed
 Program is closed
 COMMIT command is used
 Syntax:
 COMMIT [WORK];
 Will permanently save any changes made to
any table in the database
17

 SELECT
 Used to list contents of table
 Syntax:
 SELECT columnlist
FROM tablename;
 Columnlist represents one or more attributes,
separated by commas
 Asterisk can be used as wildcard character to list
all attributes
18

 UPDATE
 Modify data in a table
 Syntax:
 UPDATE tablename
SET columnname = expression [, columname =
expression]
[WHERE conditionlist];
 If more than one attribute is to be updated
in row, separate corrections with commas
19

ROLLBACK
 Used to restore database to its previous
condition
 Only applicable if COMMIT command has not
been used to permanently store changes in
database
Syntax:
 ROLLBACK;
COMMIT and ROLLBACK only work with
data manipulation commands that are
used to add, modify, or delete table rows
20

 DELETE
 Deletes a table row
 Syntax:
 DELETE FROM tablename
[WHERE conditionlist ];
 WHERE condition is optional
 If WHERE condition is not specified, all rows
from specified table will be deleted
21

 INSERT
 Inserts multiple rows from another table (source)
 Uses SELECT subquery
 Query that is embedded (or nested) inside another query
 Executed first
 Syntax:
 INSERT INTO tablename SELECT columnlist FROM
tablename;
22

 Select partial table contents by placing
restrictions on rows to be included in output
 Add conditional restrictions to SELECT
statement, using WHERE clause
 Syntax:
 SELECT columnlist
FROM tablelist
[ WHERE conditionlist ] ;
23

 BETWEEN
 Used to check whether attribute value is within a
range
 IS NULL
 Used to check whether attribute value is null
 LIKE
 Used to check whether attribute value matches
given string pattern
25

 IN
 Used to check whether attribute value matches
any value within a value list
 EXISTS
 Used to check if subquery returns any rows
26

 All changes in table structure are made by
using ALTER command
 Followed by keyword that produces specific
change
 Following three options are available:
 ADD
 MODIFY
 DROP
27

 ALTER can be used to change data type
 Some RDBMSs (such as Oracle) do not permit
changes to data types unless column to be
changed is empty
28

 Aggregate functions
 Special functions that perform arithmetic
computations over a set of rows
 ORDER BY clause
 Used to sort output of SELECT statement
 Can sort by one or more columns and use either
an ascending or descending order
 Join output of multiple tables with SELECT
statement
29

 Natural join uses join condition to match
only rows with equal values in specified
columns
 Right outer join and left outer join used to
select rows that have no matching values in
other related table
30

Introduction to structured query language (sql) (1)

More Related Content

What's hot

Similar to Introduction to structured query language (sql) (1)

More from RajniKashyap9

Recently uploaded

Introduction to structured query language (sql) (1)