Lab 2

Database Systems (SE-312A)
LAB# 02
Objective:
Introduction to SQL Server and SQL
Introduction
• SQL stands for Structured Query Language

• SQL lets you access and manipulate databases
• SQL became a standard of the American National Standards Institute (ANSI) in 1986,
and of the International Organization for Standardization (ISO) in 1987
• SQL can execute queries against a database
• SQL can retrieve data from a database
• SQL can insert records in a database
• SQL can update records in a database
• SQL can delete records from a database
• SQL can create new databases
• SQL can create new tables in a database
• SQL can create stored procedures in a database
• SQL can create views in a database
• SQL can set permissions on tables, procedures, and views
Using SQL in Your Web Site
To build a web site that shows data from a database, you will need:
• An RDBMS database program (i.e. MS Access, SQL Server, MySQL)

• To use a server-side scripting language, like PHP or ASP
• To use SQL to get the data you want
• To use HTML / CSS to style the page
RDBMS
• RDBMS stands for Relational Database Management System.

• RDBMS is the basis for SQL, and for all modern database systems such as MS SQL
Server, IBM DB2, Oracle, MySQL, and Microsoft Access.
• The data in RDBMS is stored in database objects called tables. A table is a collection of
related data entries and it consists of columns and rows.
• Every table is broken up into smaller entities called fields. The fields in the Customers
table consist of CustomerID, CustomerName, ContactName, Address, City, PostalCode
and Country. A field is a column in a table that is designed to maintain specific
information about every record in the table.
Page 1 of 5
• A record, also called a row, is each individual entry that exists in a table. For example,
there are 91 records in the above Customers table. A record is a horizontal entity in a
table.
• A column is a vertical entity in a table that contains all information associated with a
specific field in a table.
SQL Process:
When you are executing an SQL command for any RDBMS, the system determines the
best way to carry out your request and SQL engine figures out how to interpret the task.
Following is a simple diagram showing SQL Architecture:
Tables
• The data in RDBMS is stored in database objects called tables. A table is a collection of
related data entries and it consists of columns and rows.
• Every table is broken up into smaller entities called fields. The fields in the Customers
table consist of CustomerID, CustomerName, ContactName, Address, City, PostalCode
and Country. A field is a column in a table that is designed to maintain specific
information about every record in the table.
Page 2 of 5
• A record, also called a row, is each individual entry that exists in a table. For example,
there are 91 records in the above Customers table. A record is a horizontal entity in a
table.
• A column is a vertical entity in a table that contains all information associated with a
specific field in a table.
In relational database systems (DBS) data are represented using tables (relations). A query issued
against the DBS also results in a table. A table has the following structure:
Example Database
In the following example we use a demo database to manage information about Customers.
The Customer table has the format:
For the attributes, the following data types are defined:
EMPNO:number(4), ENAME:varchar2(30), JOB:char(10), MGR:number(4),
HIREDATE:date, SAL:number(7,2), DEPTNO:number(2)
DEPT table:
Page 3 of 5
The SQL SELECT Statement

The SELECT statement is used to select data from a database. The result is stored in a
result table, called the result-set.
SQL SELECT Syntax
SELECT column_name,column_name
FROM table_name;
and
SELECT * FROM table_name;

Selecting Columns
The columns to be selected from a table are specified after the keyword select. This
operation is also called projection. For example 1.1,
SELECT CustomerName, CustomerID FROM Customers;
It lists only the name and the IDs for each tuple from the relation Customer.
The SQL WHERE Clause
The WHERE clause is used to extract only those records that fulfill a specified criterion.
SQL WHERE Syntax

SELECT column_name,column_name
FROM table_name
WHERE column_name operator value;
The SQL ORDER BY Keyword

The ORDER BY keyword is used to sort the result-set by one or more columns.
The ORDER BY keyword sorts the records in ascending order by default. To sort the
records in a descending order, you can use the DESC keyword.
Page 4 of 5
SQL ORDER BY Syntax
SELECT column_name, column_name
FROM table_name
ORDER BY column_name ASC|DESC, column_name ASC|DESC;
Lab Tasks:
1. Open SQL Server Management Studio, Login with sa and password 123.
2. Run the example 1.1 query and record the results.
3. Display ids, names and addresses from Customers table.
4. Display the record of Customers who lives in London.
5. Display names, ids countries of Customers sorted by Country.
Page 5 of 5

Lab 2

Uploaded by

Copyright:

Available Formats

Lab 2

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Lab 2

Uploaded by

Copyright:

Available Formats

Database Systems (SE-312A)

• SQL stands for Structured Query Language

Using SQL in Your Web Site

• An RDBMS database program (i.e. MS Access, SQL Server, MySQL)

• RDBMS stands for Relational Database Management System.

Following is a simple diagram showing SQL Architecture:

The Customer table has the format:

For the attributes, the following data types are defined:

EMPNO:number(4), ENAME:varchar2(30), JOB:char(10), MGR:number(4),

HIREDATE:date, SAL:number(7,2), DEPTNO:number(2)

The SQL SELECT Statement

SELECT * FROM table_name;

SELECT CustomerName, CustomerID FROM Customers;

The SQL WHERE Clause

SQL WHERE Syntax

The SQL ORDER BY Keyword

You might also like