Databases and Sql

1. Databases and Sql

2. Introduction • Relation: Relation means table(data is arranged in rows and columns) • Domain : A domain is a pool of values appearing in given column.Eg: Supplier # Domain (S001,S002,S003,SOO4)

3. Tuple : The rows of a relation • Attribute : The columns of a relation. Attributes Tuple

4. Degree : Number of attributes(columns) • Cardinality: Number of tuples(rows) Degree 2 Cardinality 4

5. Key • Primary Key: Set of one of one more attribute that can uniquely identify tuples within a relation • Candidate key: is one or more column in table that uniquely identify each row in a table (can act as primary key). • Alternate Key: candidate key is not a primary key

6. Data types SQL data types are classified into three • Numeric • String • Date

7. 1. Numeric data type: - Length of numeric data type 1-255 - Range decimal places ( 0 to 30) - decimal must be 2 less than length - 2 categories of integer data types are integer(whole numbers) data type TINYINT, SMALLINT, MEDIUMINT, INT, INTEGER, BIGINT • Floating point data types are FLOAT,DOUBLE, DOUBLEPRECISION, REAL, DECIMAL, DEC, • NUMERIC, FIXED

8. 2. String data types - used to store various types of data - four categories of string data types are CHARACTER, BINARY , TEXT and LIST commonly used data type is character • character is classified in two Char uses n byte storage (blank spaced filled with space) varcharuses less storage space

9. 3. Date & Time -yyyy –mm-dd format categories of date & time data types DATE, TIME, DATETIME,YEAR, TIMESTAMP

10. SQL Statements • Sql is made up of set of statements that define the structure of database, store and manage data within that structure and control access to data. Sql statements are mainly divided in to three categories • DDL(Data Definition Language) • DML(Data Manipulation Language) • DCL(Data Control Language)

11. DDL -( Data Definition Language ) DDL statements create, alter and delete data structure within table. DDL statements defines the type.Eg: create, alter, Drop • DML – (Data Manipulation Language ) DML is concerned with data stored in a table. Eg: Insert, update, delete and select. • DCL – (Data Control Language) DCL statements controlling the access to database. Eg: grant and revoke.

12. CREATE TABLE command Create table <tablename> (<column name> <type> [<SIZE>][NOT NULL|NULL][DEFAULT <VALUE> ] [PRIMARY KEY(<column name>],[CHECK EXPRESSION][UNIQUE] ,..)

13. Example create table student ( rollnoint primary key, column level definition name char (10) );

14. Create a table student using following information create table student ( Rollnoint(4) Primary key , Name varchar(10) Total int(2)); • you can’t define a primary key on multiple columns at the column level

15. INSERT Command To insert row in table Syntax 1 : INSERT INTO <table name> (<column name>, <column name><column name>..) VALUES (<value>,<value><value><value>..);Eg: Insert into student (rollno, name,total) values (12101,’arun’,400); if column name are included value clause must include a value for each column

16. Format 2 INSERT INTO <table name> VALUES (<value>,<value><value><value>..), VALUES (<value>,<value><value><value>..), VALUES (<value>,<value><value><value>..), Insert into student values (12101,’arun’,400), values(12102,’anil’, 500);

17. INSERT INTO <table name> VALUES (<value>,<value><value><value>..), (<value>,<value><value><value>..), Eg: Insert into student values (12101,’arun’,400), (12102,’anil’, 500), (12103,’amit’,550), (12104,neem,450);

18. *Points to remember inserting number : <value> inserting char : <’value’> inserting date :<’yyyy-mm-dd’) inserting null : NULL • if column name are not included you must provide value for every column in table

19. UPDATE Command • To modify the existing row content UPDATE <table name> SET <column name = expression>[where <expression>]; where clause determines the which row in a table are updated. Eg : Update student set total =450 where rollno=12101;

20. DELETE Command To remove row /data from a table DELETE FROM <table name> [where expression]; Eg : delete from student where roll = 12102;

21. SELECT Command To retrieve data from MySql tables we can use SELECT statement SELECT {ALL | DISTINCT } {* | <column name > | <expression > [AS] <alias> } FROM {<table name>}{,[<table name>]}{…….} [WHERE <expression>] [GROUP BY <column name>] [HAVING <expression>] [ORDER BY <column name [ASC|DESC]>]

22. Select command let you to make queries on the database Form1: To retrive columns Select <colum name>[, colum name, colum name …] from <table name>; Eg : select rollno, name from student; Form 2: if you want to see the entire table. The (*) can be substituted . Select * from <table name> Eg: Select * from student;

23. Select with - DISTINCT clause or ALL • To eliminate duplicates rows from result of a SELECT statement. Form 3 Select DISTINCT column name from <table name> Eg: Select distinct name from student; Form 4 Select ALL column name from <table name> Eg: Select ALL name from student;

24. Retrieving specific rows – where clause Select <colum name>[, colum name, colum name …] from <table name> where <condition>; Eg: select * from student where rollno=12101; Relational operators: <(less than) ,>(Greater than),<=(less than or equal),>=(greater than or equal), =(Equal) ,!= or <>(Not equal). Logical operator : and, or not

25. Between clause – to set range Select <colum name>[, colum name, colum name …] from <table name> [where column name between value1 and value 2 ; The above statement means Select <colum name>[, colum name, colum name …] from <table name> [where column name>= value1 and column name <= value 2 ; Eg: select * from student where total between 400 and 500. select * from student where total >= 400 and total<= 500.

26. IN clause – to select values Select <colum name>[, colum name, colum name …] from <table name> [where column name IN( value1,value 2) ; The above statement means Select <colum name>[, colum name, colum name …] from <table name> [where column name= value1 or column name < value 2 ; Eg: select * from student where total IN(400, 500). select * from student where total = 400 or total = 500.

27. Order by clause – for Sorting - Arrange rows in either ascending / descending order Select <column name>[, colum name, colum name …] from <table name> [where <condition> order by <column name>[asc/desc]] Default sorting ascending order(asc) Eg: select * from student where total >= 400 and total<= 500 order by name.

28. Group by – for grouping similar records Select <column name>[, column name, column name …] from <table name>[ group by <column name> having <condition>] If you are using group by clause and you want to restrict rows use having clause instead of where. Eg: select * from student where group by total having total >= 400 and total<= 500.

29. Group functions • MAX() -> Returns Maximum value from a given column • MIN() -> Returns Maximum value from a given column • AVG() -> Returns Average value from a given column • SUM()-> Returns Sum of values in a given column • COUNT() -> count number of rows (Excluding NULL) : COUNT(*) -> returns number of rows( including null)

30. Group function - Example • Select Sum(Total) from student; • Select Max(Total) from student; • Select Min(Total) from student; • Select Avg(Total) from student; • Select count() from student; • Select Count(*) from student;

31. Thank you