Spread Knowledge

CS403 - Database Management Systems - Lecture Handout 28

User Rating:  / 0
PoorBest 

Related Content: CS403 - VU Lectures, Handouts, PPT Slides, Assignments, Quizzes, Papers & Books of Database Management Systems

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 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: -

COURSE (crCode, crName, crCredits, prName)

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

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
1 S1020 Sohail Dar MCS 2.8
2 S1038 Shoaib Ali BCS 2.78
3 S1015 Tahira Ejaz MCS 3.2
4 S1034 Sadia Zia BIT  
5 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
1 Sohail Dar MCS
2 Shoaib Ali BCS
3 Tahira Ejaz MCS
4 Sadia Zia BIT
5 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
1 Sohail Dar MCS
2 Shoaib Ali BCS
3 Tahira Ejaz MCS
4 Sadia Zia BIT
5 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:

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
1 BCS
2 BIT
3 MCS
4 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.