SQL

1. Lecture 11 SQL PROF. Sin-Min LEE Department of Computer Science San Jose State University

2. Introduction • What is SQL? • Motivation • How is it used

3. What is SQL ? • Non-procedural Language • Data Definition • Data Management • Data Manipulation (Query)

5. Motivation • Powerful • Easy to learn • Flexible • ANSI/ISO Standard

6. Data Definition The SQL Data Definition Language (DDL) allows us to create and destroy database objects such as schemas, domains, tables, views, and indexes. The ISO standard also allows the creation of assertions, character sets, collations and translations.

7. The main SQL data definition language statements are: • CREATE SCHEMA • DROP SCHEMA • CREATE DOMAIN • ALTER DOMAIN • DROP DOMAIN

8. CREATE TABLE • ALTER TABLE • DROP TABLE • CREATE VIEW • DROP VIEW While CREATE INDEX and DROP INDEX are provided by DBMS

10. How is it used ? • As a Data Definition Language • Create the database and its table structure • Create the tables - CREATE TABLE command •  Entity integrity •  Referential integrity • automatically enforced

11. How is it used ? • CREATE TABLE Library ( • 1 L_Code VCHAR(10) NOT NULL UNIQUE • 2 L_City VCHAR(10) NOT NULL • 3 L_Size INTEGER NOT NULL • 4 B_Code INTEGER • 5 • 6 PRIMARY KEY (L_Code), • 7 FOREIGN KEY (B_Code ) );

12. How is it used ? • BasicData Management Commands • INSERT  Add Data to the Table • SELECT  Check the Table Contents • COMMIT  Save the Table Contents • UPDATE  Make changes • DELETE  Delete Table Rows • ROLLBACK  Restore Table Contents

15. SQL IDENTIFIER SQL Identifiers are used to identify objects in the database. The characters that can be used in a userdefined SQL identifier must appear in a character test. The following restrictions are imposed on an identifier:

16. An identifier can be no longer than 128 characters (most dialects have a much lower limit than this ). • An identifier must start with a letter. • An identifier can not contain spaces.

17. The ISO SQL Data Types There are six SQL scalar data types defined in the ISO standard, which are: character, bit, exact numeric, approximate numeric, datetime, and interval. Sometimes for manipulation and conversion purposes, the data types character and bit are collectively referred to as string data types, and exact numeric and approximate numeric are referred to as numeric data types, as they share similar properties.

26. Queries • Based on SELECT COMMAND • * is wildcard character - gives “all rows” • Use mathematical or logical operators to restrict output • Ordered lists with ORDER BY • SQL numeric functions COUNT, MIN, MAX, AVG, & SUM

27. Queries • Partial Listing of Table Contents • SELECT <column(s)> • FROM <table name> • WHERE <condition> • SELECT Tiger • FROM ANIMAL • WHERE A_class = Bengal

28. Queries • Logical Operators: AND, OR, & NOT • SELECT Tiger • FROM ANIMAL • WHERE A_class = Bengal • OR A_class = Asian

29. Queries • SPECIAL OPERATORS • BETWEEN - define range limits • IS NULL - check if attribute value is null • LIKE - check for similar character strings • IN - check if attribute value matches a value within a (sub)set of listed values • EXISTS - check whether attribute has a value

30. DEFINITION • SQL(Structured Query Language) is the industry standard relational query language. It was first designed at IBM. There are several versions of SQL standards(SQL - 86, SQL - 89, X/Open, SQL - 92, SQL3). Each vendor also has its own extensions of the SQL language • DDL(Data Definition Language) are the commands that create a database schema • DML(Data Manipulation Language) are the commands that deal with data in a database(inserts, deletes, updates, and data retrieval)

31. Differences between Tables and Relations • Tables typically allow duplicates • Attributes names are ordered in a table • Cannot always identify a candidate key or a primary key in a table

32. Example relations: DEPT dname location EECS Cory Math Evans DEPT2 dname location EECS Cory CS Soda STUDENT name regno gpa level dept Mike 1 3.5 3 EECS Jenny 2 3.8 4 Math COURSE cname cno dept Database CS57 CS Discrete Math Math55 Math TAKE regno cno 1 CS57 1 Math 55 2 CS57

33. DDL Commands • Create a table: create table: table_name attributes_description Example: create table STUDENT (name char(30) not null, regno integer not null, gpa float null, level smallint default 1 not null, dept varchar(20) null)

34. Cont. • Where default specifies a default value for the column, not null indicates that null values are not allowed for that attribute. If you do not specify null or not null SQL Server uses not null by default

35. SQL Server Data Types • int, integer: 4 byte integer • smallint: 2 byte integer • tinyint: 1 byte integer • float: 4 or 8 byte floating point number • real: 4 byte floating point number • double precision: 8 bype floating point number • numeric, decimal(precision, scale): exact numeric, 2 to 17 bytes. Only difference is that only numeric types with a scale of 0 can be used of the IDENTITY column. • Destroy a table: drop tabletable_name

36. SQL Server Data Types-cont. • Char(length) : fixed length character string. 255 characters or less • varchar(length): variable length character string. 255 characters or less • nchar(length), nvarchar(length): for multibyte character sets • text: up to 2G bytes, storage is multiples of 2 K • datetime: 8 bytes • money: 8 bytes • other data types: binary, varbinary, image, bit

37. Basic Insert: Inserting a Tuple into a Table • Insert one tuple into table: insert intotable_name[(column_list)] values(value_list) • Ex: specify values for all attributes: insert into STUDENT values(‘Mike’, 1, 3.8, 3, ‘CS’) • Ex: specify values for only some attributes: insert into STUDENT(name, regno, level) values (‘Jenny’, 2, 1) • NOTE: in SQL, strings are surrounded by single quote, not double quote. Double quotes surround identifiers that contain special characters. To include a single quote in the string use two single quotes (eg.,’it’’s good’)

38. Basic Select: Retrieving Data from One Table • Names of all students: select name from STUDENT • use distinct to eliminate duplicates: select distinct name from STUDENT

39. Basic Select - cont • use built-in function(SQL Server specific): select getdate() select datename(month, getdate()) select user NOTE: the from clause is required in the SQL standard • have expression in the select list(convert gps to 5.0 scale): select regno, gpa / 4.0 * 5.0 from STUDENT • default type conversion between all numeric data types and money

40. Basic Select - cont. • Select all attributes of a table: select * from STUDENT • Select a subset of tuples(CS seniors): select name, regno from STUDENT where dept = ‘CS’ and level = 4

41. Basic Select - cont. • Sort the results of a select. All students in ascending order of name. For students with the same name, sort in descending order of regno. Ascending is the default sor order select name, regno from STUDENT order by name asc, regno desc NOTE: order by cannot used inside a subquery

42. Joins: Select Condition Involving Multiple Tables • All students taking CS 57 select s.* from STUDENT s, TAKE t where s.regno = t.regno and t.cno = ‘cs57’ s and t in the from clause are called correlation names • Departments located in the same building select distinct d1.name, d2.name from DEPT1 d1, DEPT d2 where d1.location = d2.location

43. Joins: cont. • Get rid of combination of same attributes values: select d1.name, d2.name from DEPT d1, DEPT d2 where d1.location = d2.location and d1.name > d2.name • Avoid duplicate attributes names by renaming attributes select d1.name as name1, d2.name as name2 from DEPT d1, DEPT d2 • All employees who make more than their manager table: EMP (name, salary, manager) select e.name, m.name where e.manager = m.name and e.salary > m.salary

44. Joins: cont. • Join 3 tables: all student taking cs classes select distinct s.* from STUDENT s, TAKE t, COURSE c where s.regno = t.regno and t.cno = c.no and c.dept = ‘CS’ • All students taking classes from their own department select distinct s.* from STUDENT s, TAKE t, COURSE c where s.regno = t.regno and t.cno = c.cno and c.dept = s.dept

45. Joins: cont. Join 4 tables: all students taking classes offered by departments located in Evans. select distinct s.* from STUDENT s, TAKE t, COURSE c, DEPT d where s.regno = t.regno and t.cno = c.cno and c.dept = d.dname and d.location = ‘Evans’ All students taking CS classes: their names, regno, and the CS classes they take select s.name, s.regno, c.cno from STUDENT s, TAKE t, COURSE c where s.regno = t.regno and t.cno = c.cno and c.dept = ‘CS’ order by s.regno, s.name, c.cno

46. Outer Join • All students and their department location select s.name, d.location from STUDENT s, DEPT d where s.dept *= d.dname *= includes all rows from the first table =* includes all rows from the second table: select s.name, d.location from STUDENT s, DEPT d where d.dname = *s.dept

47. Aggregate Functions • There are five of them : count, sum, avg, max, min • Count number of student names select count (distinct name) from STUDENT • Count number of students select count (*) from STUDENT • Count distinct names of senior student in CS select count(distinct name) from STUDENT s where s.level = 4 and s.dept = ‘CS’ • All aggregate functions always return one value. If the table is empty, count returns 0, the other return null. • Get aggregates of groups by using group by: get every dept’s gpa average select dept, avg(gpa) from STUDENT group by dept Each expression in the target list must be grouping columns or aggregates

48. Aggregate Functions-cont • Get gpa average of each dept and level select dept, level, avg(gpa) from STUDENT group by dept, level • Get seniors average gpa for each dept select dept, avg(gpa) from STUDENT s where s.level = 4 group by s.dept • Restrict aggregate values of groups by using having: get all depts with more than 40 senior student select dept from STUDENT s where s.level = 4 group by s.dept having count(*) > 40 When there is no group by, the entire table is considered a group

49. Nested Queries • All students who is in a dept located in Evans select s.* from student s where dept in (select d.dname from dept d where d.location = ‘Evan’ Conceptually, the inner subquery is evaluated first to get all the depts located in Evans. Then the outer query is evaluated. This query can also be formulated using join

50. [not]exists • Exists returns true if its argument set is not empty, and false otherwise. Not exists is particularly useful for a certain type of queries. • Courses taken by any CS seniors(at least one CS senior taking the courses) select c.* from COURSE c where exists (select s.* from STUDENT s, TAKE t where s.level = 4 and s.dept = ‘CS’ and s.regno = t.regno and t.cno = c.cno)