Database Management System Lab 4 th Semester, CSE

1. Database Management System Lab 4th Semester, CSE

2. Introduction to SQL • A standard language used in most DBMS. • Well, not as standardized as one might hope • it keeps involving and growing • Vendors have the tendency to add “unique” features. • Pronounced as “S-Q-L” or “Sequel.” • Both as a DDL and DML language. • DDL (Data Definition Language): define the schema of the database. • DML (Data Manipulation Language): provides commands to manipulate the database (query, insert, update, delete).

3. SQL Continued • Based on relational algebra, but not entirely identical. • Relations  Tables • Tuples  Rows • Attributes  Columns • Unlike a relation, a table is not a set. Duplicates are not automatically removed. • This is for practical reasons. Duplicate eliminations are inefficient in implementation. • Like a relation, the order of rows in a table is irrelevant.

4. Basic DDL Commands in SQL • CREATE: to define new tables (to define relation schemas) • DROP: to delete table definitions (to delete relation schemas) • ALTER: to change the definitions of existing tables (to change relation schema) • Other features as DDL • Specify referential integrity constraints (FKs) • Specify user-defined attributes constraints

5. Basic DML Commands in SQL • INSERT: to add new rows to table • UPDATE: to change the “state” (the value) of rows. • DELETE: to remove rows • SELECT: a query command that uses relation algebra likeexpressions • Various options available to handle the enforcement/violation of integrity constraints

6. SQL Commands Are Sequential • Commands are executed in the order they are encountered. • DDL commands are not like C/Java declarations. • DDL and DML commands can be mixed • For example, you can define a table, fill it up with contents, and delete a columns. • That is, table definitions (relation schema) can be changed during the lifespan of a database. • The ability of doing so does imply it is a good practice. • It is best the schema/design of a database is well thought through before its use.

7. Platforms of This Course • Platform : MySQL • Open source, free software • Available on Windows and Linux. • Easily installed on your own PC.

8. MySQL • Open source and free • Generally not as powerful as Oracle • Still, it is an industrial strength package. • Users include Amazon, NASA, Google, Yahoo … • A commercial edition is also available (MySQL Enterprise) --- You are paying for the services.

9. SQL- Data Definition Language andData Manipulation Language

10. To understand the SQL Data Definition Language and Data Manipulation Language • Create • Insert • Delete • Drop • Truncate • Alter

11. Section I Data Definition Language Introduction

12. DDLCreating a Database • To initialize a new database: • Syntax: CREATE DATABASE database_name • There are numerous arguments that go along with this command but are database specific • Only some databases require database to be created and space to be allocated prior to creation of tables. • Some databases provide graphical user interfaces to create databases and allocate space. • Access only allows database to be created using User Interface

13. DDLCreating a Table • Syntax CREATE TABLE table_name (Column_name datatype[(size)], Column_name datatype[(size)], ) • Example CREATE TABLE STUDENT (SNUM VARCHAR(10), SNAME VARCHAR(20), MAJOR VARCHAR(20), LEVEL VARCHAR(10), DOB DATE); • Creates a table with five columns

14. DDLData Types • Following broad categories of data types exist in most databases: • String Data • Numeric Data • Temporal Data • Large Objects

15. DDLString Data • Fixed Length: • Occupies the same length of space in memory no matter how much data is stored in them. • Syntax: char(n) where n is the length of the String e.g. name char(50) • If the variable stored for name is ‘Presidency’ the extra 40 fields are padded with blanks

16. DDLString Data • Variable Length string is specified with maximum length of characters possible in the string, however, the allocation is sized to the size of the data stored in memory. • Syntax: Varchar(n) – n is the maximum length of data possible for the type • There may be a restriction in the maximum length of the data that you can specify in the declaration which will vary according to the database. • All character data has to be enclosed in single quotes during specification.

17. DDLNumeric Data Types • Store all the data related to purely numeric data. • Some numeric data may also be stored as a character field e.g. zip codes • Common Numeric Types: • Decimal Floating point number • Float Floating point number • Integer(size) Integer of specified length • Money A number which contains exactly two digits after the decimal point • Number A standard number field that can hold a floating point data

18. DDLTemporal Data Types • These represent the dates and time: • Three basic types are supported: • Dates • Times • Date-Time Combinations • MySQL comes with the following data types for storing a date or a date/time value in the database: • DATE - format YYYY-MM-DD • DATETIME - format: YYYY-MM-DD HH:MI:SS • TIMESTAMP - format: YYYY-MM-DD HH:MI:SS • YEAR - format YYYY or YY

19. DDLLarge Data Objects • These are used for storing data objects like files and images: • There are two types: • Character Large Objects (clobs) • Binary Large Objects (blobs)

20. Constraints in SQL: • NOT NULL - Ensures that a column cannot have a NULL value • UNIQUE - Ensures that all values in a column are different • PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table • FOREIGN KEY - Uniquely identifies a row/record in another table • CHECK - Ensures that all values in a column satisfies a specific condition • DEFAULT - Sets a default value for a column when no value is specified

21. DDLSpecifying Keys- Introduction • Unique keyword is used to specify keys. • This ensures that duplicate rows are not created in the database. • Both Primary keys and Candidate Keys can be specified in the database. • Once a set of columns has been declared unique any data entered that duplicates the data in these columns is rejected. • Specifying a single column as unique: • Example CREATE TABLE Student (snum Number, sname varchar(20), major varchar(10), level char(2), UNIQUE (name)) • Here the name column has been declared as a candidate key

22. DDLSpecifying Keys- Multiple Columns • Specifying multiple columns as unique: • Example: CREATE TABLE Student (snum Number, sname varchar(20), major varchar(10), level varchar(10), UNIQUE(snum, sname)); • Here both name and snum combination are declared as candidate keys.

23. DDLSpecifying Keys- Primary Key • Specifying multiple columns as unique: • To specify the Primary Key the Primary Key clause is used • Example: CREATE TABLE Student ( snum Number, sname varchar(20), major varchar(10), level varchar(10), DOB DATE, PRIMARY KEY (snum), UNIQUE (sname), )

24. DDLSpecifying Keys- Single and MultiColumn Keys • Single column keys can be defined at the column level instead of at the table level at the end of the field descriptions. • MultiColumn keys still need to be defined separately at the table level CREATE TABLE Student ( snum Number PRIMARY KEY, snamevarchar(20) UNIQUE, major varchar(10), level varchar(10), DOB date, Unique(DOB,MAJOR))

25. DDLSpecifying Keys- Foreign Keys • References clause is used to create a relationship between a set of columns in one table and a candidate key(primary key) in the table that is being referenced. • Example: CREATE TABLE ENROLL ( cname varchar(20) references Course(cname) , snum Number REFERENCES Student(snum)) • Creates a relationship between Student and Course.

26. The Mgr_ssn Example CREATE TABLE DEPARTMENT ( … Mgr_ssn CHAR(9), … FOREIGN KEY (Mgr_ssn) REFERENCES EMPLOYEE (Ssn) ON DELETE??? ON UPDATE ??? ); Note: Refer the EMP relation in slide no.28

27. Referential Integrity Options • Causes of referential integrity violation for a foreign key FK (consider the Mgr_ssn of DEPARTMENT). • On Delete: when deleting the foreign tuple • What to do when deleting the manager tuple in EMPLOYEE ? • On Update: when updating the foreign tuple • What to do when updating/changing the SSN of the manager tuple in EMPLOYEE is changed ? • Actions when the above two causes occur. • Set Null: the Mgr_ssn is set to null. • Set Default: the Mgr_ssn is set to the default value. • Cascade: the Mgr_ssn is updated accordingly • If the manager is deleted, the department is also deleted.

28. Referential Integrity Options An Example: Create table EMP(…ESSN CHAR(9),DNO INTEGER DEFAULT 1,SUPERSSN CHAR(9),PRIMARY KEY (ESSN),FOREIGN KEY (DNO) REFERENCES DEPTON DELETE SET DEFAULT ON UPDATE CASCADE, FOREIGN KEY (SUPERSSN) REFERENCES EMP ON DELETE SET NULL ON UPDATE CASCADE);

29. DDLConstraints- Disallowing Null Values Disallowing Null Values: • Null values entered into a column means that the data in not known. • These can cause problems in Querying the database. • Specifying Primary Key automatically prevents null being entered in columns which specify the primary key   • Not Null clause is used in preventing null values from being entered in a column. • Example: CREATE TABLE Student ( snum number PRIMARY KEY, sname varchar(20) NOT NULL, major varchar(10) NOT NULL, level varchar(10) NOT NULL DOB date) • Null clause can be used to explicitly allow null values in a column also

30. DDLConstraints- Value Constraints Value Constraints: • Allows value inserted in the column to be checked condition in the column constraint. • Check clause is used to create a constraint in SQL • Example: CREATE TABLE STUDENT (snum Number PRIMARY KEY, sname varchar(20), Age Number check (Age > = 50000) ) • Table level constraints can also be defined using the Constraint keyword • Example: CREATE TABLE STUDENT (SNUM Number PRIMARY KEY, sname varchar(20) not null, age Number , CONSTRAINT age_constraint Check (age between 17 and 22)) • Such constraints can be activated and deactivated as required.

31. DDLConstraints- Default Value Default Value: • A default value can be inserted in any column by using the Default keyword. • Example: CREATE TABLE STUDENT( SNUM NUMBER NOT NULL UNIQUE PRIMARY KEY, SNAME varchar(20) NOT NULL, MAJOR VARCHAR(10) DEFAULT ‘CSE’, DOB DATE DEFAULT NULL );

32. DDLConstraints- AUTO INCREMENT • Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table. • Often this is the primary key field that we would like to be created automatically every time a new record is inserted. • The following SQL statement defines the "ID" column to be an auto-increment primary key field in the "Persons" table: • CREATE TABLE STUDENT(    SNUM int NOT NULL AUTO_INCREMENT,    LastName varchar(255) NOT NULL,    FirstName varchar(255),    Age int,    PRIMARY KEY (SNUM));

33. continue... • MySQL uses the AUTO_INCREMENT keyword to perform an auto-increment feature. • By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record. • To let the AUTO_INCREMENT sequence start with another value, use the following SQL statement: • ALTER TABLE STUDENT AUTO_INCREMENT=100; • To insert a new record into the “STUDENT" table, we will NOT have to specify a value for the “SNUM" column (a unique value will be added automatically): • INSERT INTO STUDENT (Sname,Major, Level,DOB)VALUES ('Lakshman', ‘IS‘, ’JR’, ‘2001-05-01’);

34. Section II Modifying Records

35. Modifying RecordsInsert Statement • Insert: • Allows you to add new records to the Table • Syntax: • Insert into table_name[(column_list)] values (value_list) • Example: INSERT INTO student VALUES (1, ‘Ganesh’, ‘CSE’, ‘JR’, ’2000-05-01’) INSERT INTO Student (snum, sname, major, level, DOB) VALUES (2, ‘ramesh’, ‘CSE’, ‘SR’, ’2000-07-31’)) • Notes1: If the columns are not specified as in the first example the data goes in the order specified in the table • Notes2: There are two ways of inserting Null values 1. If the field has a default value of Null, you can use an Insert statement that ignores the column where the value is to be Null. 2. You can specify the column in the column list specification and assign a value of Null to the corresponding value field.

36. Modifying RecordsSelect & Insert • Select & Insert: • A select query can be used in the insert statement to get the values for the insert statement • Example: INSERT INTO ENROLL SELECT snum FROM student • This selects the corresponding fields from the student table and inserts them into the enroll table.

37. Modifying RecordsDelete Statement • Delete Statement: • is used to remove records from a table of the database. The where clause in the syntax is used to restrict the rows deleted from the table otherwise all the rows from the table are deleted. • Syntax: DELETE FROM table_name [WHERE Condition] • Example: DELETE FROM STUDENT WHERE SNAME = ‘Ramesh’ • Deletes all the rows where the sname is ‘Ramesh’ keeps all the other rows.

38. Modifying RecordsUpdate Statement • Update Statement: • used to make changes to existing rows of the table. It has three parts. First, you ,must specify which table is going to be updated. The second part of the statement is the set clause, in which you should specify the columns that will be updated as well as the values that will be inserted. Finally, the where clause is used to specify which rows will be updated. • Syntax: UPDATE table_name SET column_name1 = value1, column_name2 = value2, ….. [WHERE Condition] • Example: UPDATE STUDENT SET SNAME = ‘Vignesh’, MAJOR = ‘IS’ WHERE snum = 1

39. Modifying RecordsTruncate Statement • Truncate Statement: • used to delete all the rows of a table. Delete can also be used to delete all the rows from the table. The difference is that delete performs a delete operation on each row in the table and the database performs all attendant tasks on the way. On the other had the Truncate statement simply throws away all the rows at once and is much quicker. The note of caution is that truncate does not do integrity checks on the way which can lead to inconsistencies on the way. If there are dependencies requiring integrity checks we should use delete. • Syntax: TRUNCATE TABLE table_name • Example: TRUNCATE TABLE STUDENT; • This deletes all the rows of the table STUDENT;

40. Modifying RecordsDrop Statement • Drop Statement: • used to remove elements from a database, such as tables, indexes or even users and databases. Drop command is used with a variety of keywords based on the need. • Drop Table Syntax: DROP TABLE table_name • Drop Table Example: DROP TABLE STUDENT; • Drop Index Syntax: DROP INDEX table_name • Drop Index Example: DROP INDEX student_index

41. Modifying RecordsAlter Statement • Alter Statement: • used to make changes to the schema of the table. Columns can be added and the data type of the columns changed as long as the data in those columns conforms to the data type specified. • Syntax: ALTER TABLE table_name ADD (column datatype [Default Expression]) [REFERENCES table_name (column_name)’ [CHECK condition] • Example: ALTER TABLE STUDENT ADD (SEMESTER Number DEFAULT 4)

42. Modifying RecordsAlter Statement Add table level constraints: • Syntax: ALTER TABLE table_name ADD ([CONSTRAINT constraint_name CHECK comparison] [columns REFERENCES table_name (columns)] • Example: ALTER TABLE studios ADD (CONSTRAINT check_state CHECK (studio_state in (‘TX’, ‘CA’, ‘WA’))

43. Modifying Records Modify Columns: • Syntax: ALTER TABLE table_name MODIFY column [data type] [Default Expression] [REFERENCES table_name (column_name)’ [CHECK condition] • Example: ALTER TABLE STUDENT MODIFY MAJOR varchar(2)

44. Modifying RecordsAlter Statement • Alter Statement: • used to make changes to the schema of the table. Columns can be added and the data type of the columns changed as long as the data in those columns conforms to the data type specified. • Syntax: ALTER TABLE table_name ADD (column datatype [Default Expression]) [REFERENCES table_name (column_name)’ [CHECK condition] • Example: ALTER TABLE studios ADD (revenue Number DEFAULT 0)

45. Delete Columns from Existing Tables • To delete column SNUM alter table EMPLOYEE drop column SNUM; Note: changing relation schemas typically indicates ill-executed design phase of the database.

46. Miscellaneous Commands • show databases; • Show all the databases on the server • show tables; • Show all the tables of the present database • show columns from table EMPLOYEE; • drop table t_name; • Delete the entire table t_name • drop database db_name; • Delete the entire database db_name