Queries in SQL Syntax and semantics Creative Commons

Queries in SQL Syntax and semantics Creative Commons License – Curt Hill

Select is the basic query command • The most common form is: SELECT field 1, field 2…fieldn FROM table 1, table 2…tablem WHERE condition • Select f. name, c. dept, c. number, c. crhr From faculty as f, course as c, faculty_teach as ft Where f. naid=ft. naid AND ft. dept = c. dept AND ft. number = c. number Creative Commons License – Curt Hill

Syntax • The statement is free form – May be on one line or many – Words, constants and strings must be complete on one line • Punctuation is minimal – Commas in lists • My prefererance – Select, From, Where on separate lines – One condition per line Creative Commons License – Curt Hill

Three pieces • Select – Describes the resulting table • From – Lists the tables that will be used to generate the final table • Where – Gives the conditions or comparisons • Join fields • Selection comparisons – Where is optional Creative Commons License – Curt Hill

Select • List fields desired • This may include constants and calculations • Field names may be qualified by table names if needed • Corresponds to the project of algebra • Use * to get all fields • Many clauses to be discussed later Creative Commons License – Curt Hill

From • Specify the tables • Allows renaming the table for convenience in the rest of the query • Form is: FROM Tab 1, Tab 2 AS t, Tab 3 u – The AS reserved word renames for the rest of the statement – May be left out • Only tables mentioned here may be accessed Creative Commons License – Curt Hill

Example Without Where • Select name, degree From Faculty • Select * From Faculty, Faculty_Teach – Cartesian Product Creative Commons License – Curt Hill

Where • Supply conditions • Compare the field name with a value – Does the selection part • Compare two fields from different tables – Does the join • Is actually optional but leaving it out gives the whole table or a cartesian product – Not a join Creative Commons License – Curt Hill

Where Comparisons • Comparison operators –= –> –< – >= – <> Creative Commons License – Curt Hill

Where boolean operators • • And Or Not () Creative Commons License – Curt Hill

Disclaimer • All the examples in this presentation use a previous schema of the college database • The old schema has all the same tables and fields, but most of the names have changed Creative Commons License – Curt Hill

Student Score Example • Select name, dept, course, score From Students, Grades Where Students. naid=Grades. naid • Naid need qualification, since it exists in both, rest are unique • From allows an alias – Or rename or synonym • Select name, dept, course, score From Students s, Grades as g Where S. naid=G. naid Creative Commons License – Curt Hill

Quotes • There are two forms of quote on your keyboard – Apostrophes: ‘ – Double quotes: “ • SQL usually prefers apostrophes to enclose strings – Some servers will take either • They must match – Same starting as ending Creative Commons License – Curt Hill

Course a faculty member teaches • Select name, ft. dept, course from faculty, faculty_teach ft where faculty. naid = ft. naid • Put the credit hours in: • Select name, ft. dept, course, crhr from faculty, faculty_teach ft, course c where faculty. naid = ft. naid AND ft. dept=c. dept AND ft. number = c. number Creative Commons License – Curt Hill

All students who got a B or better in any CS class • Select name From grades, students Where score>=80 and students. naid = grades. naid And dept = ‘CS’ Creative Commons License – Curt Hill

Syntax Again • Reserved words and names are not sensitive to case • Blanks, tabs, line feeds and other white space are ignored • Layout only affects readability • The order of things following Select determines the table order • The order of tables following From does not matter • The order of conditions does not matter Creative Commons License – Curt Hill

Designing Queries • Start with the From clause – What tables are needed – Will duplicates of these be needed – You may add tables later if needed • Make the Where next – Consider joins first – Consider selection condition next • Finally design the Select – This is the final projection that shows the fields that you will see Creative Commons License – Curt Hill

Considering Joins • How are tables connected? • Two tables can be joined on any sets of fields provided: – The number and type of each set matches • Names do not need to match • The types are defined in the create table statement – One set of linking conditions is needed for each pair of tables • Especially look at foreign keys – Foreign keys in one that are primary in the other Creative Commons License – Curt Hill

Get This!!!! • The overwhelming majority of multitable queries involve an equality condition between a foreign key in one table and the primary key in another • This condition may be two conditions if the primary and foreign keys are both multi-field – Such as department and number for a course • This join conditions are ANDed Creative Commons License – Curt Hill

Find all students that each faculty member teaches • Select f. name, s. name from faculty as f, students s, faculty_teach ft, grades g where s. naid = g. naid AND f. naid = ft. naid And ft. dept = g. dept AND ft. number = g. number Creative Commons License – Curt Hill

Faculty members and their department and divisional chair • Multiple copies are needed for multiple names • Select f. name, dp. name, dv. name from faculty as f, faculty dp, faculty dv, departments dept, division div where f. dept = dept AND dept. chair = dp. naid And dept. division = division AND div. chair = dv. naid Creative Commons License – Curt Hill

Aliases Again • It is often the case that we create an alias in the From clause – When we need two instances of the same table • The alias may be created with an As – But the As may also be left out • We may also do an alias in the Select – But never need to do so Creative Commons License – Curt Hill

Select Alias • If we do a select from a client, the column is headed by the field name – Access from a program does not see these in the same way • We can use the alias feature to put a character string there instead • Something like this: Select f_name As ‘Faculty Name’ • We may leave the As off Creative Commons License – Curt Hill

Variants • Each server may accept a slightly different syntax • Oracle does not allow an alias in the Select to be enclosed in apostrophes – They must be in quotes or without any – It will not accept quotes anywhere else • My. SQL and Maria. DB will accept apostrophes or quotes anywhere Creative Commons License – Curt Hill

Finally • This is the basic syntax of a query • There are many other options and clauses – These await further presentations • Next we must do a demonstration • Then your first SQL assignment Creative Commons License – Curt Hill