ZeePedia Add to Favourites   |   Contact us


Database Management Systems

<<< Previous Select Statement, Attribute Allias Next >>>
 
img
Database Management System (CS403)
VU
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
img
Database Management System (CS403)
VU
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
img
Database Management System (CS403)
VU
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
212
img
Database Management System (CS403)
VU
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:
213
img
Database Management System (CS403)
VU
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.
214
Table of Contents:
  1. Introduction to Databases and Traditional File Processing Systems
  2. Advantages, Cost, Importance, Levels, Users of Database Systems
  3. Database Architecture: Level, Schema, Model, Conceptual or Logical View:
  4. Internal or Physical View of Schema, Data Independence, Funct ions of DBMS
  5. Database Development Process, Tools, Data Flow Diagrams, Types of DFD
  6. Data Flow Diagram, Data Dictionary, Database Design, Data Model
  7. Entity-Relationship Data Model, Classification of entity types, Attributes
  8. Attributes, The Keys
  9. Relationships:Types of Relationships in databases
  10. Dependencies, Enhancements in E-R Data Model. Super-type and Subtypes
  11. Inheritance Is, Super types and Subtypes, Constraints, Completeness Constraint, Disjointness Constraint, Subtype Discriminator
  12. Steps in the Study of system
  13. Conceptual, Logical Database Design, Relationships and Cardinalities in between Entities
  14. Relational Data Model, Mathematical Relations, Database Relations
  15. Database and Math Relations, Degree of a Relation
  16. Mapping Relationships, Binary, Unary Relationship, Data Manipulation Languages, Relational Algebra
  17. The Project Operator
  18. Types of Joins: Theta Join, Equi–Join, Natural Join, Outer Join, Semi Join
  19. Functional Dependency, Inference Rules, Normal Forms
  20. Second, Third Normal Form, Boyce - Codd Normal Form, Higher Normal Forms
  21. Normalization Summary, Example, Physical Database Design
  22. Physical Database Design: DESIGNING FIELDS, CODING AND COMPRESSION TECHNIQUES
  23. Physical Record and De-normalization, Partitioning
  24. Vertical Partitioning, Replication, MS SQL Server
  25. Rules of SQL Format, Data Types in SQL Server
  26. Categories of SQL Commands,
  27. Alter Table Statement
  28. Select Statement, Attribute Allias
  29. Data Manipulation Language
  30. ORDER BY Clause, Functions in SQL, GROUP BY Clause, HAVING Clause, Cartesian Product
  31. Inner Join, Outer Join, Semi Join, Self Join, Subquery,
  32. Application Programs, User Interface, Forms, Tips for User Friendly Interface
  33. Designing Input Form, Arranging Form, Adding Command Buttons
  34. Data Storage Concepts, Physical Storage Media, Memory Hierarchy
  35. File Organizations: Hashing Algorithm, Collision Handling
  36. Hashing, Hash Functions, Hashed Access Characteristics, Mapping functions, Open addressing
  37. Index Classification
  38. Ordered, Dense, Sparse, Multi-Level Indices, Clustered, Non-clustered Indexes
  39. Views, Data Independence, Security, Vertical and Horizontal Subset of a Table
  40. Materialized View, Simple Views, Complex View, Dynamic Views
  41. Updating Multiple Tables, Transaction Management
  42. Transactions and Schedules, Concurrent Execution, Serializability, Lock-Based Concurrency Control, Deadlocks
  43. Incremental Log with Deferred, Immediate Updates, Concurrency Control
  44. Serial Execution, Serializability, Locking, Inconsistent Analysis
  45. Locking Idea, DeadLock Handling, Deadlock Resolution, Timestamping rules