1 / 8

CS 111 – Nov. 10

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.

wray
Download Presentation

CS 111 – Nov. 10

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 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

  2. 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.

  3. Example An abridged Employee table:

  4. 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.

  5. 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;

  6. 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;

  7. 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;

  8. 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

More Related