CS 222 Lecture 7 DATABASE MANAGEMENT SYSTEM Somchai

CS 222 Lecture 7 DATABASE MANAGEMENT SYSTEM Somchai Thangsathityangkul

Create demo data • Create database csdb; • Use csdb; • Copy file mysqlsampledatabase. sql to c: /temp/ • Source c: /temp/mysqlsampledatabase. sql; 2

ER-Diagram of Sample data 3

SELECT with WHERE • Expressions in a WHERE clause can use the following types of operators: • Arithmetic: +, -, *, /, DIV, % • Comparison: <, <=, =, <=>, <> , >=, >, BETWEEN • Logical: AND, OR, NOT • Additional options: IN, IS NULL, LIKE 4

Order precedence operators Arithmetic: * / div % +- High order Lower order Logical: Not And Or Highest order High order Lowest order 5

Using SELECT Clauses Select first. Name, last. Name, office. Code From employees Where office. Code < 3 ; This will show first. Name, last. Name and office. Code which office code less than 3 6

Using SELECT Clauses Select first. Name, last. Name, This will show first. Name, last. Name and office. Code which office code = 1 or =4 office. Code From employees Where office. Code = 1 or office. Code = 4 ; 7

Using SELECT Clauses Select first. Name, last. Name, This will show employee which office code = 4 and jobtitle = sales rep office. Code, jobtitle From employees Where office. Code = 4 and Jobtitle = ‘Sales Rep’ ; 8

Using SELECT Clauses Select customer. Number, payment. Date, amount From payments; This will show all cutomer payment date and amount. 9

Using SELECT Clauses Select customer. Number, payment. Date, amount This will show all cutomer payment date and amount between 5000 to 8000. From payments Where amount between 5000 and 8000; 10

Using SELECT Clauses Select customer. Number, payment. Date, amount This will show all cutomer payment date and payment date between 2005/01/01 to 2005/02/25. From payments Where payment. Date between 20050101 and 20050225; 11

Using SELECT Clauses Select customer. Name, This will show all cutomer name and address. Line 1, address. Line 2 From customers; 12

Using SELECT Clauses Select customer. Name, address. Line 1, address. Line 2 This will show all cutomer name and address which address line 2 is not null. From customers Where address. Line 2 is not null; 13

Using SELECT Clauses Select first. Name, last. Name, This will show employee which office code in the list of 1, 3, 5. office. Code, jobtitle From employees Where office. Code in ( 1, 3, 5 ); 14

Using SELECT Clauses Select first. Name, last. Name, This will show employee which office code not in the list of 1, 3, 5. office. Code, jobtitle From employees Where office. Code Not in ( 1, 3, 5 ); 15

Using SELECT Clauses Select customer. Number, payment. Date, amount This will show all cutomer payment date and amount not between 1500 to 110000. From payments Where amount Not between 1500 and 110000; 16

Using SELECT Clauses Select first. Name, last. Name, This will show employee which office code = 4 and jobtitle = sales rep union with office code = 6 office. Code, jobtitle From employees Where office. Code = 4 and Jobtitle = ‘Sales Rep’ or office. Code = 6 ; 17

Using SELECT Clauses Select first. Name, last. Name, This will show employee which office code = 4 and jobtitle = sales rep union with office code = 6 office. Code, jobtitle From employees Where (office. Code = 4 and Jobtitle = ‘Sales Rep’) or office. Code = 6 ; 18

Using SELECT Clauses Select first. Name, last. Name, office. Code, jobtitle This will show employee which job title = sales rep and office code = 6 then uion with job title = sale manager (EMEA) From employees Where jobtitle = ‘Sale Manager (EMEA)’ Or jobtitle = ‘Sales Rep’ and office. Code = 6 ; 19

Using SELECT Clauses Select first. Name, last. Name, office. Code, jobtitle This will show employee which job title = sales rep and office code = 6 then uion with job title = sale manager (EMEA) From employees Where jobtitle = ‘Sale Manager (EMEA)’ Or (jobtitle = ‘Sales Rep’ and office. Code = 6 ); 20

Using SELECT Clauses Select first. Name, last. Name, office. Code, jobtitle This will show employee which job title = sale manager (EMEA) or job title = sales rep then must have office code =6 From employees Where (jobtitle = ‘Sale Manager (EMEA)’ Or jobtitle = ‘Sales Rep’) and office. Code = 6 ; 21