Effective SQL Query Practices for Database Management

1. CS146 References: ORACLE 9i PROGRAMMING A Primer Rajshekhar Sunderraman http://www.answers.com/ http://www.wikipedia.org/

2. Where ? • Run sql queries directly from http://phpmyadmin.student.seas.gwu.edu/

3. Tables • A relation from the relational data model translates to a table. STUDENTS=(SID,FNAME,LNAME,MINIT)

4. CREATING A TABLE IN SQL create table students ( sid varchar(5), fname varchar(20), lname varchar(20), minit char); VIEW TABLE STRUCTURE describe students; MODIFYING A TABLE alter table students add ( gender char(1) ); alter table students drop column gender; alter table students modify fname varchar(40); DELETING A TABLE drop table students;

5. BASIC DATA TYPES

6. Adding, deleting and modifying data • select * from students; • insert into students values ('1111','Nandita','Rajshekhar','K'); • update students set lname=′Elaine′ where sid=1111; • delete from students where sid=1111;

7. Create insert statements to insert the following values into the table

8. Simple select statements • select fname from students; • select sid,fname from students; • select * from students; • select minit from students where fname=′Sydney′; • select * from students where fname like ′S%′; • select * from students where minit is not null; • select fname from students where sid between 1111 and 3333;

9. Simplified select statement select [distinct] <expression>{,<expression>} from <tablename> [<alias>] {,<tablename> [<alias>] } [where <search_condition>]

10. Adding more tables

11. JOIN • problem : finding out the term and the section each student is registered in • A join combines records from two or more tables in a relational database. • A join is essentially a cartesian product • It is usually followed by a predicate to filter the results.

12. Join example select fname,term from students,enrolls where students.sid = enrolls.sid;

13. Types of Join • Cross join • Inner join • Left outer join • Right outer join • Full outer join

14. Cross join • While not used very commonly, a cross join is the foundation upon which inner joins are built. A cross join returns the cartesian product of the sets of rows from the joined tables. • The SQL code for a cross join lists the tables to be joined (FROM), but does not include any filtering predicate (WHERE). select fname,term from students,enrolls;

15. Inner join • An inner join essentially finds the intersection between the two tables. This is the most common type of join used, and is considered the default join type. • The join example below takes all the records from table A (in this case, students) and finds the matching record(s) from table B (enrolls). If no match is found, the record from A is not included in the results. If multiple results are found in B that match the predicate then one row will be returned for each (the values from A will be repeated). • Special care must be taken when joining tables on columns that can be NULL since NULL values will never match each other. select fname,term from students,enrolls where students.sid = enrolls.sid;

16. Left outer join • Instead of limiting results to those in both tables, it limits results to those in the "left" table (A). This means that if the ON clause matches 0 records in B, a row in the result will still be returned—but with NULL values for each column from B. select fname , term from enrolls left outer join students on enrolls.sid = students.sid;

17. Right outer join • Same as a left outer join, except that the tables are reversed. Every record from the right side, B , will be returned, and NULL values will be returned for those that have no matching record in A. select fname , term from enrolls right outer join students on enrolls.sid = students.sid;

18. Full outer join-not supported my MySql (Oracle supports it) • A full outer join combines the results of both left and right outer joins. These joins will show records from both tables, and fill in NULLs for missing matches on either side. select fname , term from enrolls full outer join students on enrolls.sid = students.sid;