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-6th Edition 3.12 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 3.12 ©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 from instructor Database System Concepts-6th Edition 3.13 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 3.13 ©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 N rows(number of tuples in the instructors table),each row with value“A” Database System Concepts-6th Edition 3.14 ©Silberschat乜，Korth and Sudarshan
Database System Concepts - 6 3.14 ©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 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 Database System Concepts-6th Edition 3.15 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 3.15 ©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
The where Clause The where clause specifies conditions that the result must satisfy Corresponds to the selection predicate of the relational algebra. To find all instructors in Comp.Sci.dept select name from instructor where dept_name Comp.Sci.' Comparison results can be combined using the logical connectives and,or,and not To find all instructors in Comp.Sci.dept with salary 80000 select name from instructor where dept_name Comp.Sci.'and salary 80000 Comparisons can be applied to results of arithmetic expressions. Database System Concepts-6th Edition 3.16 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 3.16 ©Silberschatz, Korth and Sudarshan th Edition The where Clause The where clause specifies conditions that the result must satisfy Corresponds to the selection predicate of the relational algebra. To find all instructors in Comp. Sci. dept select name from instructor where dept_name = ‘Comp. Sci.' Comparison results can be combined using the logical connectives and, or, and not To find all instructors in Comp. Sci. dept with salary > 80000 select name from instructor where dept_name = ‘Comp. Sci.' and salary > 80000 Comparisons can be applied to results of arithmetic expressions