Select Statement, Attribute Allias

<< Alter Table Statement

Data Manipulation Language >>

Database Management System (CS403)

Lecture No. 28

Reading Material

"Database Management Systems", 2nd edition, Raghu Ramakrishnan, Johannes Gehrke,

McGraw-Hill

"Teach Yourself SQL in 21 Days", Second Edition Que Series.

In the previous lecture we started the data manipulation language, in which we were

discussing the Insert statement, which is used to insert data in an existing table. In

today's lecture we will first see an example of Insert statement and then discuss the

other SQL Commands.

The INSERT statement allows you to insert a single record or multiple records into a

table. It has two formats:

INSERT INTO table-1 [(column-list)] VALUES (value-list)

And,

INSERT INTO table-1 [(column-list)] (query-specification)

The first form inserts a single row into table-1 and explicitly specifies the column

values for the row. The second form uses the result of query-specification to insert

one or more rows into table-1. The result rows from the query are the rows added to

the insert table. Both forms have an optional column-list specification. Only the

columns listed will be assigned values. Unlisted columns are set to null, so unlisted

columns must allow nulls. The values from the VALUES Clause (first form) or the

columns from the query-specification rows (second form) are assigned to the

corresponding column in column-list in order. If the optional column-list is missing,

the default column list is substituted. The default column list contains all columns in

table-1 in the order they were declared in CREATE TABLE.

The VALUES Clause in the INSERT Statement provides a set of values to place in

the columns of a new row. It has the following general format:

VALUES (value-1 [, value-2]...)

Value-1 and value-2 are Literal Values or Scalar Expressions involving literals. They

can also specify NULL. The values list in the VALUES clause must match the

210

Database Management System (CS403)

explicit or implicit column list for INSERT in degree (number of items). They must

also match the data type of corresponding column or be convertible to that data type.

We will now see an example of INSERT statement for that we have the table of

COURSE with following attributes: -

(crCode, crName, crCredits, prName)

COURSE

The INSERT statement is as under:

INSERT INTO course VALUES (`CS-211', `Operating Systems', 4, `MCS')

This is a simple INSERT statement; we have not used the attribute names because we

want to enter values for all the attributes. So here it is important to enter the values

according to the attributes and their data types. We will now see an other example of

insert statement:

INSERT INTO course (crCode, crName) VALUES (`CS-316', Database

Systems')

In this example we want to enter the values of only two attributes, so it is important

that other two attributes should not be NOT NULL. So in this example we have

entered values of only two particular attributes. We will now see another example of

INSERT statement as under:

INSERT INTO course (`MG-103', `Intro to Management', NULL, NULL)

In this example we have just entered the values of first two attributes and rest two are

NULL. So here we have not given the attribute names and just placed NULL in those

values.

Select Statement

Select statement is the most widely used SQL Command in Data Manipulation

Language. It is not only used to select rows but also the columns. The SQL SELECT

statement queries data from tables in the database. The statement begins with the

SELECT keyword. The basic SELECT statement has 3 clauses:

SELECT

FROM

WHERE

211

Database Management System (CS403)

The SELECT clause specifies the table columns that are retrieved. The FROM clause

specifies the tables accessed. The WHERE clause specifies which table rows are used.

The WHERE clause is optional; if missing, all table rows are used. The SELECT

clause is mandatory. It specifies a list of columns to be retrieved from the tables in the

FROM clause. The FROM clause always follows the SELECT clause. It lists the

tables accessed by the query. The WHERE clause is optional. When specified, it

always follows the FROM clause. The WHERE clause filters rows from the FROM

clause tables. Omitting the WHERE clause specifies that all rows are used. The

syntax for the SELECT statement is:

SELECT {*|col_name[,....n]} FROM table_name

This is the simplest form of SELECT command. In case of * all the attributes of any

table would be available. If we do not mention the * then we can give the names of

particular attribute names. Next is the name of the table from where data is required.

We will now see different examples of SELECT statement using the following table:

STUDENT

stId

stName

prName

cgpa

S1020

Sohail Dar

MCS

2.8

S1038

Shoaib Ali

BCS

2.78

S1015

Tahira Ejaz

MCS

3.2

S1034

Sadia Zia

BIT

S1018

Arif Zia

BIT

3.0

So the first query is

Q: Get the data about studentsSELECT * FROM students

The output of this query is as under:

stId

stName

prName

cgpa

S1020

Sohail Dar

MCS

2.8

S1038

Shoaib Ali

BCS

2.78

S1015

Tahira Ejaz

MCS

3.2

S1034

Sadia Zia

BIT

S1018

Arif Zia

BIT

3.0

We will now see another query, in which certain specific data is required form the

table: The query is as under:

Q: Give the name of the students with the program nameThe SQL Command for the

query is as under:

SELECT stName, prName

FROM student

The output for the command is as under:

stName

prName

Sohail Dar

MCS

212

Database Management System (CS403)

Shoaib Ali

BCS

Tahira Ejaz

MCS

Sadia Zia

BIT

Arif Zia

BIT

Attribute Allias

SELECT {*|col_name [[AS] alias] [, ...n]} FROM tab_name

Now in this case if all the attributes are to be selected by * then we cannot give the

name of attributes. The AS is also optional here then we can write the name of

attribute what we want. We will now see an example.

SELECT stName as `Student Name', prName `Program' FROM Student

The output of this query will be as under:

Student Name

Program

Sohail Dar

MCS

Shoaib Ali

BCS

Tahira Ejaz

MCS

Sadia Zia

BIT

Arif Zia

BIT

In the column list we can also give the expression; value of the expression is

computed and displayed. This is basically used where some arithmetic operation is

performed, in which that operation is performed on each row and then that result is

displayed as an output. We will now see it with an example:

Q Display the total sessional marks of each student obtained in each subject

The SQL Command for the query will be as under:

Select stId, crCode, mTerm + sMrks `Total out of 50' from enroll

The DISTINCT keyword is used to return only distinct (different) values. The

SELECT statement returns information from table columns. But what if we only want

to select distinct elements With SQL, all we need to do is to add a DISTINCT

keyword to the SELECT statement. The format is as under:

213

Database Management System (CS403)

SELECT DISTINCT column_name(s)

FROM table_name

We will now see it with an example

Q Get the program names in which students are enrolled

The SQL Command for this query is as under:

SELECT DISTINCT prName FROM Student

programs

BCS

BIT

MCS

MBA

The "WHERE" clause is optional. When specified, it always follows the FROM

clause. The "WHERE" clause filters rows from "FROM" clause tables. Omitting the

WHERE clause specifies that all rows are used. Following the WHERE keyword is a

logical expression, also known as a predicate. The predicate evaluates to a SQL

logical value -- true, false or unknown. The most basic predicate is a comparison:

Color = 'Red'

This predicate returns:

True -- If the color column contains the string value -- 'Red',

False -- If the color column contains another string value (not 'Red'), or

Unknown -- If the color column contains null.

Generally, a comparison expression compares the contents of a table column to a

literal, as above. A comparison expression may also compare two columns to each

other. Table joins use this type of comparison.

In today's we have studied the SELECT statement with different examples. The

keywords SELECT and FROM enable the query to retrieve data. You can make a broad

statement and include all tables with a SELECT * statement or you can rearrange or

retrieve specific tables. The keyword DISTINCT limits the output so that you do not see

duplicate values in a column. In the coming lecture we will see further SQL

Commands of Data Manipulation Language.

214

Table of Contents: