Transcript nov10
CS 111 – Nov. 10 • Structured Query Language (SQL) – We’ve already seen simple select statements, with optional “where” clause and aggregate functions. – More example commands today • Relational database • Commitment – Review for test after lab Log in • As you will see in lab, you first need to “log in” to the database management system in order to see your database. • From a Web browser, go to cs.furman.edu/phpMyAdmin/ • Enter your database username & password • (Demo) • If your SQL commands are incorrect, you’ll get error message. Example An abridged Employee table: First Last Location Title Salary Peter Jacobs Brussels Broker 55000 Denise Lambert Brussels Accountant 42500 Robert Nijs Brussels Broker 66700 Ruth Molloy Chicago Manager 68650 Declan Murphy Chicago Accountant 84125 Susan Patterson Chicago Economist 51000 Rachel Brady Cincinnati Broker 43300 David Cunningham Cincinnati Accountant 48000 John Whelan Cincinnati Broker 60500 Yvonne Butler San Diego Broker 48500 Veronica Keating San Diego Broker 72000 Mary Walsh Dublin Accountant 46850 Distinct • Sometimes in SQL you have a lot of repeated data. And you only want the values themselves, not the repetitions. – Ex. 70, 70, 70, 80, 80, 80, 80, 80, 100 70, 80, 100 • Examples select Test2 from Student; gives all test grades select distinct Test2 from Student; only shows each value once select distinct Test1, Test2 from Student; finds distinct pairs. 70/60, 70/80, and 90/80 are considered distinct. Group by • The “group by” clause is good at finding subtotals. • Example: how many employees by job title: – Select count(first) from Employee group by Title; – Actually, doesn’t matter which field we count. • To make output easier to understand, we should also print out the job titles: – Select Title, count(Salary), avg(Salary) from Employee group by title; • We can even subtotal by 2 fields: What would this command mean? – Select Location, Title, count(Salary) from Employee group by Location, Title; Order by • This clause is used for sorting. • Default order is ascending. • select * from Employee order by Last; • select * from Employee order by Location, Last; • select * from Employee where Salary > 50000 order by Location, Title; More on “where” • Boolean conditions: when you use the “where” clause can include the word “and” or “or” to make complex conditions. – Ex. What if we wanted salaries of employees with names starting with M or P. • Use “in” when you want to select among several possible values. Has the same effect as “or” – Select * from Employee where Location in (“Dublin”, “Chicago”); • Use “between” for an (inclusive) range of values to check – Select * from Employee where Salary between 60000 and 70000; Relational Database • Databases with just 1 table are not very powerful. • More interesting if 2 tables are related. – Books and publishers – Customers and orders – Pets and owners • Typically we have a “one-to-many” relationship • The two tables need to have a field in common. – You can see this if you try to list the necessary fields in the above examples. • In SQL, to refer to a field within some table, we use the dot notation: Customer.First, Pet.Name, Order.ID