Download Database Management System CS403 Handouts and more Lecture notes Database Management Systems (DBMS) in PDF only on Docsity!
Lecture No. 25
Reading Material
“Database Management Systems”, 2nd^ edition, Raghu Ramakrishnan, Johannes Gehrke, McGraw-Hill
Overview of Lecture
o Structured Query Language (SQL)
In the previous lecture we have studied the partitioning and replication of data. From this lecture onwards we will study different rules of SQL for writing different commands.
Rules of SQL Format
SQL, at its simplest, is a basic language that allows you to "talk" to a database and extract useful information. With SQL, you may read, write, and remove information from a database. SQL commands can be divided into two main sub languages. The Data Definition Language (DDL) contains the commands used to create and destroy databases and database objects. After the database structure is defined with DDL, database administrators and users can utilize the Data Manipulation Language to insert, retrieve and modify the data contained within it. Following are the rules for writing the commands in SQL:-
- Reserved words are written in capital like SELECT or INSERT.
- User-defined identifiers are written in lowercase
- Identifiers should be valid, which means that they can start with @,_
alphabets ,or with numbers. The maximum length can be of 256. The reserved words should not be used as identifiers.
- Those things in the command which are optional are kept in [ ]
- Curly braces means required items
- | means choices
- [,…..n] means n items separated by comma
Consider the following example:- SELECT [ALL|DISTINCT]
{*|select_list}
FROM {table|view[,…n]}
Select * from std
Data Types in SQL Server
In Microsoft SQL Server™, each column, local variable, expression, and parameter has a related data type, which is an attribute that specifies the type of data (integer, character, money, and so on) that the object can hold. SQL Server supplies a set of system data types that define all of the types of data that can be used with SQL Server. The set of system-supplied data types is shown below:-
Integers:
Integer (whole number) data from –2 63 (-9,223,372,036,854,775,808) through 2^63 -1 (9,223,372,036,854,775,807).
Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647).
Integer data from -2^15 (-32,768) through 2^15 - 1 (32,767).
Integer data from 0 through 255.
bit
Integer data with either a 1 or 0 value.
Decimal and Numeric
Fixed precision and scale numeric data from -10^38 +1 through 10^38 –1.
Functionally equivalent to decimal.
Text:
It handles the textual data. Following are the different data types.
- Char: By default 30 characters, max 8000
Examination System DatabaseExamination System DatabaseExamination System DatabaseExamination System Database
We will now transfer this conceptual database design into relational database design as under:-
PROGRAM (prName, totSem, prCredits)
COURSE (crCode , crName, crCredits, prName)
SEMESTER (semName , stDate, endDate)
CROFRD (crCode, semName, facId)
FACULTY (facId , fName, fQual, fSal, rank)
STUDENT (stId , stName, stFName, stAdres,stPhone, prName, curSem, cgpa)
ENROLL (stId, crCode, semName, mTerm,sMrks, fMrks, totMrks, grade, gp)
SEM_RES (stId, semName, totCrs, totCrdts, totGP, gpa)
It is used to specify a database scheme as a set of definitions expressed in a DDL. DDL statements are compiled, resulting in a set of tables stored in a special file called
a data dictionary or data directory. The data directory contains metadata (data about data) the storage structure and access methods used by the database system are specified by a set of definitions in a special type of DDL called a data storage and definition language
Data Manipulation is retrieval, insertion, deletion and modification of information
from the database. A DML is a language, which enables users to access and
manipulate data. The goal is to provide efficient human interaction with the system.
There are two types of DML.First is Procedural: in which the user specifies what data
is needed and how to get it Second is Nonprocedural: in which the user only specifies
what data is needed
The category of SQL statements that control access to the data and to the database.
Examples are the GRANT and REVOKE statements.
Summary:
In today’s lecture we have read about the basics of SQL. It is used to communicate with a database. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems. SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database. Some common relational database management systems that use SQL are: Oracle, Sybase, Microsoft SQL Server, Access, Ingres, etc. Although most database systems use SQL, most of them also have their own additional proprietary extensions that are usually only used on their system. However, the standard SQL commands such as "Select", "Insert", "Update", "Delete", "Create", and "Drop" can be used to accomplish almost everything that one needs to do with a database. In the end we have also seen the different types of SQL commands and their functions.
Exercise:
Practice the basic commands of SQL like SELECT, INSERT and CREATE.
the current default database. The name of the table must be unique to the database. The name must begin with a letter and can be followed by any combination of alphanumeric characters. The name is allowed to contain the underscore character ( _ ). This command can be used to create permanent disk-based or temporary in- memory database tables. Data stored in a temporary table is lost when the server is shutdown. To create a temporary table the "AS TEMP" attribute must be specified. Note that querying against a temporary in-memory table is generally faster than querying against a disk-based table. This command is non-transactional. If no file size is given for a disk-based table, the table will be pre-allocated to 1MB. If no filegrowth is given, the default is 50%. It is used to create new tables, fields, views and indexes. It is used to create database. The format of statement is as under: CREATE DATABASE db_name
For Example CREATE DATABASE EXAM. So now in this example database of exam has been created. Next step is to create tables. There are two approaches for creating the tables, which are:
- Through SQL Create command
- Through Enterprise Manager
Create table command is used to:
- Create a table
- Define attributes of the table with data types
- Define different constraints on attributes, like primary and foreign keys, check constraint, not null, default value etc.
The format of create table command is as under: CREATE TABLE
[ database_name.[ owner ]. | owner. ] table_name ( { < column_definition >
| column_name AS omputed_column_expression | < table_constraint >
} | [ { PRIMARY KEY | UNIQUE } [ ,...n ] ]
Let us now consider the CREATE statement used to create the Airport table definition
for the Airline Database.
CREATE TABLE Airport
(airport char(4) not null,
name varchar(20),
checkin varchar(50),
resvtns varchar(12),
flightinfo varchar(12) );
Table Name.(Airport)
The name chosen for a table must be a valid name for the DBMS.
Column Names. (Airport, Name, ..., FlightInfo)
The names chosen for the columns of a table must also be a valid name for the DBMS.
Data Types
Each column must be allocated an appropriate data type. In addition, key columns, i.e.
columns used to uniquely identify individual rows of a given table, may be specified
to be NOT NULL. The DBMS will then ensure that columns specified as NOT NULL
always contain a value.
The column definition is explained as under:
< column_definition > ::= { column_name data_type }
[ DEFAULT constant_expression ] [ < column_constraint > ] [ ...n ]
The column constraint is explained as under:
< column_constraint > ::= [ CONSTRAINT constraint_name ]
{ [ NULL | NOT NULL ]
| [ { PRIMARY KEY | UNIQUE } ]
| [ [ FOREIGN KEY ] REFERENCES ref_table [ ( ref_column ) ]
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]
]
Every constraint should be given a meaningful name as it can be referred later by its name. The check constraint checks the values for any particular attribute. In this way different types of constraints can be enforced in any table by CREATE command.
Summary
Designing a database properly is extremely important for the success of any
application. In today’s lecture we have seen the CREATE command of SQL. How
different constraints are applied on this command with the help of different examples.
This is an important command and must be practiced as it is used to create database
and different tables. So create command is part of DDL.
Exercise:
Create a database of Exam System and create table of student with different constraints in SQL Server.
Lecture No. 27
Reading Material
“Database Management Systems”, 2nd^ edition, Raghu Ramakrishnan, Johannes Gehrke, McGraw-Hill “Teach Yourself SQL in 21 Days”, Second Edition Que Series.
Overview of Lecture
Data Manipulation Language
In the previous lecture we were studying DDL in which we studied the CREATE command along with different examples. We also saw different constraints of create command. In this lecture we will study the ALTER and other SQL commands with examples.
Alter Table Statement
The purpose of ALTER statement is to make changes in the definition of a table
already created through Create statement. It can add, and drop the attributes or
constraints, activate or deactivate constraints. It modifies the design of an existing
table. The format of this command is as under:
Syntax
ALTER TABLE table {
ADD [COLUMN] column type [(size)] [DEFAULT default] |
ALTER [COLUMN] column type [(size)] [DEFAULT default] |
ALTER [COLUMN] column SET DEFAULT default |
DROP [COLUMN] column |
RENAME [COLUMN] column TO columnNew
}
The ALTER TABLE statement has these parts:
Part Description
Table The name of the table to be altered.
Delete can also be used DROP TABLE table_name
Data Manipulation Language The non-procedural nature of SQL is one of the principle characteristics of all 4GLs - Fourth Generation Languages - and contrasts with 3GLs (eg, C, Pascal, Modula-2, COBOL, etc) in which the user has to give particular attention to how data is to be accessed in terms of storage method, primary/secondary indices, end-of-file conditions, error conditions (eg, Record NOT Found), and so on. The Data Manipulation Language (DML) is used to retrieve, insert and modify database information. Data Manipulation is retrieval, insertion, deletion and modification of
information from the database. SQL is a non-procedural language that is, it allows the user to concentrate on specifying what data is required rather than concentrating on the how to get it. There are two types of DML.First is procedural in which: the user specifies what data is needed and how to get it. Second is nonprocedural in which the user only specifies what data is needed. The DML component of SQL comprises of following basic statements: Insert To add new rows to tables. Select To retrieve rows from tables Update To modify the rows of tables
Insert The INSERT command in SQL is used to add records to an existing table. We will now see the format of insert command as under: INSERT [INTO] table {[ ( column_list ) ] { VALUES ( { DEFAULT | NULL | expression } [ ,...n] ) } } | DEFAULT VALUES The basic format of the INSERT...VALUES statement adds a record to a table using the columns you give it and the corresponding values you instruct it to add. You must follow three rules when inserting data into a table with the INSERT...VALUES statement: The values used must be the same data type as the fields they are being added to. The data's size must be within the column's size. For instance, you cannot add an 80- character string to a 40-character column.
The data's location in the VALUES list must correspond to the location in the column list of the column it is being added to. (That is, the first value must be entered into the first column, the second value into the second column, and so on.) The rules mentioned above must be followed. We will see the examples of the insert statement in the coming lectures. Summary SQL provides three statements that can be used to manipulate data within a database. The INSERT statement has two variations. The INSERT...VALUES statement inserts a set of values into one record. The INSERT...SELECT statement is used in combination with a SELECT statement to insert multiple records into a table based on the contents of one or more tables. The SELECT statement can join multiple tables, and the results of this
join can be added to another table. The UPDATE statement changes the values of one or more columns based on some condition. This updated value can also be the result of an expression or calculation. The DELETE statement is the simplest of the three statements. It deletes all rows from a table based on the result of an optional WHERE clause. If the WHERE clause is omitted, all records from the table are deleted. Modern database systems supply various tools for data manipulation. Some of these tools enable developers to import or export data from foreign sources. This feature is particularly useful when a database is upsized or downsized to a different system. Microsoft Access, Microsoft and Sybase SQL Server, and Personal Oracle7 include many options that support the migration of data between systems. Exercise: Try inserting values with incorrect data types into a table. Note the errors and then insert values with correct data types into the same table.
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.
S1038 Shoaib Ali BCS 2.
S1015 Tahira Ejaz MCS 3.
S1034 Sadia Zia BIT
S1018 Arif Zia BIT 3.
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. 2 S1038 Shoaib Ali BCS 2. 3 S1015 Tahira Ejaz MCS 3. 4 S1034 Sadia Zia BIT 5 S1018 Arif Zia BIT 3. 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
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.
Lecture No. 29
Reading Material
“Database Management Systems”, 2nd^ edition, Raghu Ramakrishnan, Johannes Gehrke, McGraw-Hill “Teach Yourself SQL in 21 Days”, Second Edition Que Series.
Overview of Lecture
Data Manipulation Language
In the previous lecture we have studied the SELECT statement, which is the most
widely used SQL statement. In this lecture we will study the WHERE clause. This is
used to select certain specific rows.
The WHERE clause allows you to filter the results from an SQL statement - select,
insert, update, or delete statement. The rows which satisfy the condition in the where
clause are selected. The format of WHERE clause is as under:
SELECT [ALL|DISTINCT]
{*|culumn_list [alias][,…..n]} FROM table_name [WHERE <search_condition>]
Here WHERE is given in square brackets, which means it is optional. We will see the search condition as under:
Search Condition
{ [ NOT ] < predicate > | ( < search_condition > ) } [ { AND | OR } [ NOT ] { < predicate > |
( < search_condition > ) } ] } [ ,...n ]
< predicate > ::=
{ expression { = | < > |! = | > | > = |! > | < | < = |! < }
