Transcript Lab 13-finalx

Lab 13
DATABASES AND SQL
Useful information

Last lab of the semester .

Next week there will be a lab exam .

No assignments for this lab.
Databases

Data :
facts and statistics collected together for reference or analysis

Database :
A database is a collection of one or more relations,
where each relation is a table with rows and columns

RDBMS :
It contains a set of relations
Database

Table
attributes
(or columns)
customer_namecustomer_street customer_city
Jones
Smith
Curry
Lindsay
Main
North
North
Park
Harrison
Rye
Rye
Pittsfield
tuples
(or rows)
RDBMS

Relation: made up of 2 parts:

Instance : a table , with rows and columns. #Rows = cardinality, #fields = degree / arity.

Schema : specifies name of relation, plus name and type of each column.

Ex. Students(sid: string, name: string, login: string, age: integer, gpa: float )
SQL –Structured Query Langauage

Developed by IBM (system R) in the 1970s
Vendors

Oracle

MYSQL

MS-SQL server

IBM DB2
SQL –Structured Query Langauage

Activity I : SELECT Tutorial from BBC TABLE
http://sqlzoo.net/1.htm
1.
Hint: SELECT >>FROM >>WHERE
SELECT is the data extraction operator.You can choose the attributes here (columns),’ * ‘ returns all the
columns from the table
FROM :Table name .
WHERE : It is used to limit the rows according to the condition/s.
SQL –Structured Query Langauage

Activity I : SELECT Tutorial from BBC TABLE

2.Relational operators same as c++


=(no double sign here like c++) ,>,<,<=,>=,!=

Note :there is no operator like !< .Just use > instead
3.per capita GDP is the GDP divided by the population GDP/population
Calculations are usually done in select clause .A new column is created with the calculation .It is a better
practice to use an alias for a calculated column .
Aliases :
Aliases are defined in the from clause, called in select and where clauses . Eg.Select t.id from table as t (
Alternatively used as “from table t” without “as”.Here we create a new column called percaptaGDP using
the formula)
SQL –Structured Query Langauage

Activity I : SELECT Tutorial from BBC TABLE

4. SQL aggregate functions returns a single value, calculated from values in a column.

Useful aggregate functions: Used on column with quantitative data
AVG() - Returns the average value
COUNT() - Returns the number of rows
ROUND()-Returns after rounding the number
MAX() - Returns the largest value
MIN() - Returns the smallest value
SUM() - Returns the sum
SQL –Structured Query Langauage
Activity I : SELECT Tutorial from BBC TABLE
6.
The SQL LIKE Operator
The LIKE operator is used to search for a specified pattern in a column.
SQL LIKE Syntax:
SELECT column name(s)
FROM table name
WHERE column name LIKE pattern;
% operator is used inside the like operator.
Ex. ‘%United’- Returns strings which ends with with United
‘United%’ - Returns string which starts with United
‘%United%’- Returns string which contains United irrespective of the location.
SQL –Structured Query Langauage

Activity I : SELECT Tutorial from BBC TABLE
5.The IN Operator

The IN operator allows you to specify multiple values in a WHERE clause.
SQL IN Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...);
SQL –Structured Query Langauage

ActIivity 2:More practice with SELECT: Additional practice of the basic features using a table of Nobel Prize
winners.
http://sqlzoo.net/1b.htm
SQL joins

An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them.

The most common type of join is: SQL INNER JOIN (simple join). An SQL INNER JOIN returns all rows from multiple tables where the join
condition is met.

http://sqlzoo.net/3.htm
Questions?