Basic Query Structure A typical SQL query has the form: select A,A2,...,An from r,r2,...rm where P A,represents an attribute R,represents a relation 。P is a predicate. The result of an SQL query is a relation. Database System Concepts-7th Edition 3.12 ©Silberscha乜，Korth and Sudarshan
Database System Concepts - 7 3.12 ©Silberschatz, Korth and Sudarshan th Edition Basic Query Structure ▪ A typical SQL query has the form: select A1 , A2 , ..., An from r1 , r2 , ..., rm where P • Ai represents an attribute • Ri represents a relation • P is a predicate. ▪ The result of an SQL query is a relation
The select Clause The select clause lists the attributes desired in the result of a query corresponds to the projection operation of the relational algebra Example:find the names of all instructors: select name from instructor NOTE:SQL names are case insensitive (i.e.,you may use upper-or lower-case letters.) ·E.g.,Name≡NAME≡name Some people use upper case wherever we use bold font. Database System Concepts-7th Edition 3.13 ©Silberscha乜，Korth and Sudarshan
Database System Concepts - 7 3.13 ©Silberschatz, Korth and Sudarshan th Edition The select Clause ▪ The select clause lists the attributes desired in the result of a query • corresponds to the projection operation of the relational algebra ▪ Example: find the names of all instructors: select name from instructor ▪ NOTE: SQL names are case insensitive (i.e., you may use upper- or lower-case letters.) • E.g., Name ≡ NAME ≡ name • Some people use upper case wherever we use bold font
The select Clause (Cont.) ■ SQL allows duplicates in relations as well as in query results. ■ To force the elimination of duplicates,insert the keyword distinct after select. Find the department names of all instructors,and remove duplicates select distinct dept name from instructor The keyword all specifies that duplicates should not be removed. select all dept name deptname from instructor Comp.Sci. Finance Music Physics History Physics Comp.Sci. History Finance Biology Comp.Sci. Elec.Eng. Database System Concepts-7th Edition 3.14 @Silberschatz,Korth and Sudarshan
Database System Concepts - 7 3.14 ©Silberschatz, Korth and Sudarshan th Edition The select Clause (Cont.) ▪ SQL allows duplicates in relations as well as in query results. ▪ To force the elimination of duplicates, insert the keyword distinct after select. ▪ Find the department names of all instructors, and remove duplicates select distinct dept_name from instructor ▪ The keyword all specifies that duplicates should not be removed. select all dept_name from instructor
The select Clause (Cont.) ■An asterisk in the select clause denotes“all attributes” select* from instructor An attribute can be a literal with no from clause select '437' ·Results is a table with one column and a single row with value“437” Can give the column a name using: select'437'as FOO An attribute can be a literal with from clause select 'A' from instructor Result is a table with one column and Nrows(number of tuples in the instructors table),each row with value "A" Database System Concepts-7th Edition 3.15 ©Silberscha乜，Korth and Sudarshan
Database System Concepts - 7 3.15 ©Silberschatz, Korth and Sudarshan th Edition The select Clause (Cont.) ▪ An asterisk in the select clause denotes “all attributes” select * from instructor ▪ An attribute can be a literal with no from clause select '437' • Results is a table with one column and a single row with value “437” • Can give the column a name using: select '437' as FOO ▪ An attribute can be a literal with from clause select 'A' from instructor • Result is a table with one column and N rows (number of tuples in the instructors table), each row with value “A
The select Clause (Cont.) The select clause can contain arithmetic expressions involving the operation,+,-*and /and operating on constants or attributes of tuples. ·The query: select ID,name,salary/12 from instructor would return a relation that is the same as the instructorrelation, except that the value of the attribute salary is divided by 12. Can rename "salary/12"using the as clause: select ID,name,salary/12 as monthly_salary Database System Concepts-7th Edition 3.16 ©Silberscha乜，Korth and Sudarshan
Database System Concepts - 7 3.16 ©Silberschatz, Korth and Sudarshan th Edition The select Clause (Cont.) ▪ The select clause can contain arithmetic expressions involving the operation, +, –, , and /, and operating on constants or attributes of tuples. • The query: select ID, name, salary/12 from instructor would return a relation that is the same as the instructor relation, except that the value of the attribute salary is divided by 12. • Can rename “salary/12” using the as clause: select ID, name, salary/12 as monthly_salary