• Database Management Systems

Prof . R A Tela

​

1

• Creating a Database

2

Step 1. Create a Database Company

CREATE DATABASE <DATABSE NAME>;

Create database company;

Step 2. USE Database

USE <DATABSE NAME>;

use company;

Step 2. SHOW TABLES

show tables;

3

Step 1. Create a TABLE

CREATE TABLE <TABLE NAME> (

<ATTRIBUTE LIST> <DATA TYPE> <CONSTRAINT>,

<ATTR2> <DATA TYPE>,<CONSTRAINT>);

Creating a Table

VIPS: Oct - Dec 2019

4

Creating a Table

create table product(Pname varchar(20) primary key, price float NOT NULL,category varchar(20) CHECK(category in("Gadget","Photography","Household")), manufacturer varchar(20));

5

Show tables;

Desc <tablename>;

Show Existing Tables

Describe structure of a Existing Table

Desc product;

6

INSERT INTO R(A1,…., An) VALUES (v1,…., vn)

Insert records in Table

insert into product(Pname,price,category,manufacturer) values("Gizmo",19.99, "Gadgets", "GizmoWorks");

or

insert into product values("Gizmo",19.99, "Gadgets", "GizmoWorks");

insert into product values("Powergizmo",29.99, "Gadgets", "GizmoWorks");

insert into product values("SingleTouch",149.99, "Photography", "Canon");

insert into product values("MultiTouch",203.99, "Household", "Hitachi");

• Select Query

7

SELECT *�FROM product;

Product

“selection”

SELECT <attributes>

FROM <one or more relations>

WHERE <conditions>

• Select Query using WHERE

8

SELECT Pname, Price�FROM Product

Product

“projection”

• Select Query using WHERE

9

SELECT *�FROM Product�WHERE category=‘Gadgets’;

Product

“selection” with

where

• Select Query using WHERE

10

SELECT PName, Price, Manufacturer�FROM Product�WHERE Price > 100;

Product

“selection” and

“projection” with

where

• Select Query using WHERE

11

SELECT PName, Price, Manufacturer�FROM Product�WHERE Price > 100 and manufacturer =“Canon”;

Product

Combine two or more

conditions Using

and

• Select Query using WHERE

12

SELECT PName, Price, Manufacturer�FROM Product�WHERE manufacturer =“Hitachi” or manufacturer = “Canon”;

Product

Combine two or more

conditions Using

or

• Select Query using WHERE

13

SELECT PName, Price, Manufacturer�FROM Product�WHERE manufacturer IN(“Hitachi”,“Canon”);

Product

Replace OR with In

conditions Using

IN

• Note That

• Case insensitive:
• Same: SELECT Select select
• Same: Product product
• Different: ‘Seattle’ ‘seattle’

​

• Constants:
• ‘abc’ - yes
• “abc” - no

14

• The LIKE operator

Pattern : pattern matching on strings. It contains two special symbols:

% = any sequence of characters

_ = any single character

15

SELECT *�FROM Products�WHERE PName LIKE <pattern>

• Like Operator with %

16

SELECT *�FROM Product�WHERE Pname like ‘p%’;

Product

Product name that starts with P

• Like Operator with %

17

SELECT *�FROM Product�WHERE Pname like ‘%Touch’;

Product

Product name that ends with Touch

• Like Operator with %

18

SELECT *�FROM Product�WHERE Pname like ‘%e%’;

Product

Product name that contains e anywhere in the name

• Like Operator with _ &%

19

SELECT *�FROM Product�WHERE Pname like ‘_o%’;

Product

Product name with second letter ‘o’

• Like Operator with %

20

SELECT *�FROM Product�WHERE Pname like ‘%c_’;

Product

Product name with second last character ‘c’

• Eliminating Duplicates

21

SELECT DISTINCT category

FROM Product;

Compare to:

SELECT category

FROM Product;

Aggregate Functions

22

Except count, all aggregations apply to a single attribute

SQL supports several aggregation operations:

​

• Sum
• Max
• Min
• Avg
• Count

Aggregate Functions – SUM

23

SELECT sum(price)�FROM Product;

Product

Sum of Price of all Products

403.96

Aggregate Functions – MAX

24

SELECT max(price)�FROM Product;

Product

Max of Price of all Products

203.96

Aggregate Functions – MIN

25

SELECT min(price)�FROM Product;

Product

Min of Price of all Products

19.99

Aggregate Functions – AVG

26

SELECT avg(price)�FROM Product;

Product

Avg of Price of all Products

100.99

Aggregate Functions – COUNT

27

SELECT count(price)�FROM Product;

Product

Total number of Products

4

SELECT count(*)�FROM Product;

More Examples

28

29

WRITE THE QUERY

• Ordering the Results

30

SELECT pname, price, manufacturer

FROM Product

WHERE manufacturer=‘GizmoWorks’ AND price > 50

ORDER BY price, pname;

• Ties are broken by the second attribute on the ORDER BY list, etc.

​

• Also works without Where

​

• Ordering is ascending, unless you specify the DESC keyword.

SELECT pname, price, manufacturer

FROM Product

ORDER BY price DESC;

31

SELECT Category

FROM Product

ORDER BY PName

?

SELECT DISTINCT category

FROM Product

ORDER BY category

SELECT DISTINCT category

FROM Product

ORDER BY PName

?

?

FIND THE RESULT

32

Practice Exercise

33

Create a new table in your current database ‘COMPANY’ with the following schema

34

Create a new table named ‘COMPDTLS’ in your current database with the following schema

COMPDTLS( CompName varchar Primary Key,

RegDate Date Not Null,

StockPrice Float

Country varchar )

• Insert the following Records in COMPDTLS

35

• Write SQL Queries for:

36

1. List the details of all companies
2. List the registration date of all companies
3. Show the details of all companies of Japan
4. List the company name whose stock price is 65
5. List the companies of Japan or India
6. Show the maximum stock price.
7. Show the average stock price.
8. Show the distinct countries
9. Show the total no of countries
10. Show the company name whose country name ends with ‘a’.