CSE 480 DatabaseSystems SQL:Structured Query Language
CSE 480 Database Systems SQL: Structured Query
Language
S. Pramanik
1
General Structure of SQL Statement
? SQL is the standard for relational query languages. ? Uses the terms
table, row, column instead of relation, tuple and attribute. ? Basic form of sql statements:
SELECT
FROM
WHERE ? Similarities with relational algebra:
? SELECT clause in SQL is similar to project () in algebra.
? WHERE clause in SQL is similar to select () ? FROM clause in SQL is similar to cross product
? in algebra.
2
Example SQLs
Tables for Example Queries
Student(Sid, Sname, Saddr, Sdno)
:S
StudentCourse(sid, cno, Instr, Grade) :SC
Department(Dno, Dname, Daddr, Chair)
:D
Course(Cno, Cname, Cdno)
:C
Student Sid Sname Saddr Sdno s1 john 2nd st. o1 s2 Mary 4th st. 02 s3 Rick Elm St. 01 s4 David Maple 03
StudentCourse sid cno Grade
s1 c1 4.0 s1 c2 3.5 s3 c2 4.0 s1 c3 4.0
Result Sid cno Grade
s1 c1 4.0 s1 c2 3.5 s1 c3 4.0 s3 c2 4.0
Query: Get names and id's for those students whose address is East Lansing.
Algebra: Sname,SidSaddr="EastLansing"(Student)
SQL:
SELECT Sname, Sid FROM Student WHERE Sddr="East Lansing"
3
More Examples
? -join: Two table names in the FROM clause, condition in the WHERE clause. Get all Student names who are in CSE dept. SnameDname="CSE"(Student Sdno=Dno Department)
Select Sname from Student, Department where Sdno=Dno and Dname="CSE"
? Conceptually cross product. ? Optimized implementation based on -join. ? Unspecified WHERE-Clause
Get all student names in the database Sname(Student)
SELECT Sname FROM Student
? Projection on all attributes: Get all student information in the student table: Student
SELECT * FROM Student
4
Duplicates in SQL
? Basic relational algebra is set theoretic and assumes no duplicates.
? Tables created by SQL with keys cannot have duplicates.
? Temporary tables created by SQL Select statements may have duplicates. Therefore, these tables may not be treated as sets. 1. Projections create duplicates. 2. Duplicate elimination expensive. 3. Keeping duplicates may be necessary (e.g., computing aggregates). 4. Use DISTINCT to remove duplicates when necessary. SELECT DISTINCT Sname FROM Student All duplicate names is removed from the result.
5
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- writing basic sql statements
- 242 31 sql set operators so handy venn you need them
- sql query cheat sheet
- proc sql for data step die hards
- chapter 8 advanced sql
- using sql queries in crystal reports
- sql commands intellipaat
- worksheet 1 sql
- using proc sql to generate shift tables more efficiently
- cse 480 databasesystems sql structured query language
Related searches
- sql query syntax checker
- export sql query to csv with headers
- sql select query syntax
- ms query sql syntax
- microsoft sql server query syntax
- sql query case statement example
- sql basic query commands
- backup query in sql server
- sql query datetime to date
- how to limit sql query results
- sql query select top 10
- sql query in pandas