1 / 64

SQL

SQL. Chapters 2 – 13, 18. Agenda. Retrieving Data Sorting Data Filtering Retrieved Data Combining filters Wildcard filters Creating Calculated Fields Using Functions Grouping Data Filtering groups Accessing Data from Multiple Tables Subqueries Joins Creating Data Views. review.

nakia
Download Presentation

SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Chapters 2 – 13, 18

  2. Agenda • Retrieving Data • Sorting Data • Filtering Retrieved Data • Combining filters • Wildcard filters • Creating Calculated Fields • Using Functions • Grouping Data • Filtering groups • Accessing Data from Multiple Tables • Subqueries • Joins • Creating Data Views

  3. review

  4. Types of SQL Commands • Data Definition Language (DDL) • define/change/remove database objects • Access to objects • Data Manipulation Language (DML) • maintain and query a data • control transaction integrity • Internet Resources • http://w3schools.com/sql/default.asp • http://www.sql-tutorial.net/

  5. Types of sql commands, cont… • DDL • Create a new database • Create tables in a database • Steps to ensure good data • Create users with access to database • DML • Add data to tables • Change data in tables • Remove data from tables

  6. More DML • Querying Data • Single Table • Multiple Tables

  7. Sql tips, practices

  8. SQL Commands, con't… • Common Practices are to use: • UPPERCASE text for reserved words • ProperCase for your names • Semi-colon to signify end of statement • Indentions and extra parentheses for readability • Meaningful names

  9. Script Files • File of one or more SQL commands • Good for batching multiple SQL commands SSMS Database Script File SQL... SQL… SQL...

  10. Script file Example /* include DROP command in case the table already exists */ Drop table student; Go /* create student table */ create table student (stud_idvarchar (9) not null, pin varchar(4), firstnamevarchar(30), lastnamevarchar(30), city varchar(20), state varchar (2), major varchar (6), classification varchar (2), gpa numeric (3,2), constraint student_stud_id_pk primary key (stud_id), constraint classification_ck check (classification in ('fr','so','jr','sr'))); Go /* add data to student table */ insert into student (stud_id,pin,firstname,lastname,city,state,major,classification,gpa) values ('123456789','1111','homer','wells','seattle','wa','mis','fr',3.00); insert into student (stud_id,pin,firstname,lastname,city,state,major,classification,gpa) values ('124567890','2121','bob','norbert','bothell','wa','fin','jr',2.7); insert into student (stud_id,pin,firstname,lastname,city,state,major,classification,gpa) values ('234567890','3321','candy','kendall','tacoma','wa','acct','jr',3.5); insert into student (stud_id,pin,firstname,lastname,city,state,major,classification,gpa) values ('345678901','1141','wally','kendall','seattle','wa','mis','sr',2.8); insert into student (stud_id,pin,firstname,lastname,city,state,major,classification,gpa) values ('456789012','2333','joe','estrada','seattle','wa','fin','sr',3.2); insert into student (stud_id,pin,firstname,lastname,city,state,major,classification,gpa) values ('567890123','0021','mariah','dodge','seattle','wa','mis','jr',3.6); insert into student (stud_id,pin,firstname,lastname,city,state,major,classification,gpa) values ('678901234','2344','tess','dodge','redmond','wa','acct','so',3.3); insert into student (stud_id,pin,firstname,lastname,city,state,major,classification,gpa) values ('789012345','2212','roberto','morales','seattle','wa','fin','jr',2.5); insert into student (stud_id,pin,firstname,lastname,city,state,major,classification,gpa) values ('876543210','0021','cristopher','colan','seattle','wa','mis','sr',4.0); insert into student (stud_id,pin,firstname,lastname,city,state,major,classification,gpa) values ('890123456','2344','luke','brazzi','redmond','wa','mis','sr',2.2); insert into student (stud_id,pin,firstname,lastname,city,state,major,classification,gpa) values ('901234567','2212','william','pilgram','seattle','wa','mis','so',3.8); Full script here: http://business.baylor.edu/gina_green/teaching/sqlserver/scripts/create_regn_tables_mis3350.sql

  11. Steps to Create/Run Scripts • Create and edit script using query editor • Save script as a file with .sql extension • Run script from SSMS • Click !Execute button • executes all commands in script --------------------------------------------- • Later, to open a previously-created script • File | Open | File…

  12. Chapter 2 retrieving Data

  13. Querying Tables • Basic Format: SELECT column(s) FROM table [WHERE condition(s)]; • Rules: • SELECT must be first clause • FROM must be second clause • Case does not matter (in SQL Server) • Table/column names must be spelled as in the database • Use double quotes for object names, single quotes for literal strings

  14. Selecting All Rows and all columns • SELECT * FROM table; • List all information for all students SELECT * FROM student;

  15. Selecting multiple columns • SELECT column1, column2, column3,…,columnX FROM table; • List IDs and names of all students SELECTstud_ID, FirstName, LastName FROM students; • List course numbers, names, and credit hours of all courses SELECTcourseNo, name, credit_hrs FROM course;

  16. Selecting Unique Rows • SELECT DISTINCT column(s) FROM table; • List all students who have registered for a class SELECTstud_ID FROM registration; SELECT DISTINCTstud_ID FROM registration;

  17. Chapter 3 Sorting data

  18. Sorting Results • ORDER BY • Sorts results in ASC or DESC order of column(s) • List names and addresses (city, state) of students in descending order of student's last and first names SELECT LastName, FirstName, city, state FROM student ORDER BY LastName, FirstName DESC; • List names and addresses (city, state) of students in descending order of student's last name, and ascending order of student's first name SELECT LastName, FirstName, city, state FROM student ORDER BY LastName DESC, FirstName ASC;

  19. Chapters 4 - 6 Filtering data

  20. Search Conditions • For retrieving specific rows: • Comparison Operators • Boolean Operators • Special Operators • Calculated Fields (Expressions) • SELECT column(s) FROM table WHERE <search condition(s)>;

  21. Comparison Operators • Comparison Operators: = equal to > greater than < less than >= greater than or equal to <= less than or equal to <> not equal to

  22. Comparison Operators • Show name and hire dates of all profs hired after 1992. SELECTFirstName, LastName, HireDate FROM prof WHERE hiredate > '12/31/1992'; • Show the classes that student 567890123 registered for. SELECTcrn FROM registration WHEREstud_id = 567890123;

  23. Boolean Operators • Boolean Operators • AND all search conditions must be met • OR any search condition must be met • NOT a search condition must not be met • Show the date that student 567890123 registered for class 9876. SELECTstud_id, crn, regn_date FROM registration WHEREstud_id = 567890123 ANDcrn = 9876;

  24. Order of evaluation • NOT, AND, OR • Use parentheses to ensure desired ordering • Show professors from the finance and MIS departments who make over $60,000 in salary. Sort results by last name. SELECTfirstname, lastname, dept, salary FROM prof WHERE dept = 'mis' OR dept = 'fin' AND salary > 60000 ORDER BYlastname; SELECTfirstname, lastname, dept, salary FROM prof WHERE (dept = 'mis' OR dept = 'fin') AND salary > 60000 ORDER BY lastname;

  25. Order of evaluation, cont… • Show professors from the finance and MIS departments, who make over $60,000 in salary, and who are not associate professors. Sort results by last name. SELECTfirstname, lastname, dept, rank, salary FROM prof WHERENOT rank = 'assc' ANDdept = 'mis' ORdept = 'fin' AND salary > 60000 ORDER BYlastname;

  26. Special Operators • Shortcuts • IN • BETWEEN • Wildcard matching • LIKE • NULL

  27. Special Operators, cont… • IN • Find class sections offered in the spring or summer of 2000 that meet on Mondays and Wednesdays. SELECT crn, courseno, secno, days, term, year FROM section WHERE term IN ('spring', 'summer') AND year = '2000' AND days = 'mw';

  28. Special Operators, cont… • BETWEEN • Find assistant professors in the Management Science and Finance departments who make between $40,000 and $100,000 in salary SELECT firstname, lastname, rank, salary, dept FROM prof WHERE dept IN ('ms', 'fin') AND rank = 'asst' AND salary BETWEEN 40000 and 100000;

  29. Special Operators, cont… • LIKE • Show courses that have "fundamental" in their names. SELECTcourseno, name FROM course WHERE Name LIKE '%Fundamental%'; • Show courses that have "fund" followed by 7 characters in their names. SELECTcourseno, Name FROM course WHERE Name LIKE '%fund_______';

  30. Special Operators, cont… • Null • unknown • not applicable • List professors who do not have a supervisor. SELECT firstname, lastname FROM prof WHERE supervisor IS NULL; • Beware… SELECT firstname, lastname FROMprof WHERE supervisor = NULL;

  31. top • Displays first N rows of query results Display the first 10 registrations. SELECT TOP 10 * FROM registration; SELECT TOP 10 * FROM registration ORDER BY regn_date; Which student had the highest final grade in class 4321? SELECT TOP 1 stud_id, final_grade FROM registration WHEREcrn = 4321 ORDER BY final_grade DESC; NOTE: works fine if only one student has highest gpa…  In the table???  Ever submitted???

  32. Top with ties • Displays all rows with the same value as the last row returned from the TOP N • Accounts for tie values, so could return more than N rows • ORDER BY clause is required Which student had the highest final grade in class 4321? SELECT TOP 1 stud_id, final_grade FROM registration WHEREcrn = 4321 ORDER BY final_grade DESC;

  33. Chapter 7 and 13 Calculated fields, expressions

  34. Column Aliasing • Assign friendly names to existing columns • Assign names to derived columns • Name only exists for duration of query • SELECTcolumn_nameAS "alias_column_name" [, ….] FROM table; • List all students who have registered for a class SELECT DISTINCTstud_IDAS "Student Number" FROM registration;

  35. Table Aliasing • Assign friendly (shortened) names to tables • Allows same table to be referenced multiple times in a query • Name only exists for duration of query • SELECTcolumn_name(s)[, ….] FROM table table_alias; • List all students who have registered for a class SELECT DISTINCTr.stud_IDAS "Student Number" FROM registration r;

  36. Expressions • Manipulating attributes in table • Show the effect of increasing professor salaries by 10% for those salaries that are currently under $50,000. SELECTprof_ID, Salary, Salary*1.1 FROM Prof WHERE Salary < 50000; • List professors who would make over $80,000 if their salaries were increased by 10%. SELECTprof_ID, salary AS "Old Pay", Salary*1.1 AS "New Pay" FROMprof WHERE salary*1.1 > 80000;

  37. Concatenating values • Connecting text values together • Show the full names (beginning with last name) and majors of senior students. SELECTlastname + ', ' + firstnameAS "fullname", major FROM student WHERE classification = 'sr';

  38. Chapters 8 and 9 functions

  39. Functions • Display, convert or manipulate values • Types • Text • Date/Time • Year, Month, Day • … • Numeric • Floor, Ceiling, Round • …. • System • System_User • GetDate() • … • Produce summary, aggregated data • COUNT • MIN • MAX • SUM • AVG

  40. Chapter 8 Non-aggregate functions

  41. Date functions • Work with dates in ways other than mm/dd/yy • Show the year that Prof. Julia Mills was hired. SELECTlastname, firstname, YEAR(hiredate) AS "Year Hired" FROMprof WHEREfirstname = 'julia' ANDlastname = 'mills'; • Show the months that students registered for classes. SELECT DISTINCTMONTH(regn_date)  month number FROM registration; SELECT DISTINCTDATENAME (MONTH, regn_date)  month name FROM registration;

  42. Numeric functions • Performs mathematical functions • Show the effects of decreasing all gpa's by 2%. SELECTlastname, firstname, gpa*.98 FROM student; SELECTlastname, firstname, ROUND(gpa*.98, 2) FROM student; SELECTlastname, firstname, FLOOR(gpa*.98) FROM student;

  43. SYSTEM functions • Retrieves information maintained by SQL Server • Who is the user currently logged on? SELECT SYSTEM_USER; • What is today's date? SELECT GETDATE(); • What professors were not hired this year? SELECTfirstname, lastname FROMprof WHERE YEAR(hiredate) <> YEAR(GETDATE());

  44. Chapter 9 Aggregate functions

  45. Count • Counts number of rows/values retrieved from query • How many students are there? SELECT COUNT(*) FROM Student; • How many registrations occurred after the year 2000? SELECT COUNT(*) FROM Registration WHERE YEAR(regn_date) > 2000; • How many professors are teaching classes? SELECT COUNT (Prof_ID)FROM Section; SELECT COUNT (DISTINCT Prof_ID)FROM Section;

  46. Min and Max • Finds minimum/maximum value of attribute Find date of most recent registration. SELECT MAX(Regn_Date) FROMRegistration; Show the earliest year that a professor was hired SELECTMIN (YEAR(hiredate)) FROMprof; Show the date that student 123456789 first registered. SELECTMIN(regn_date) FROM registration WHERE stud_ID= 123456789;

  47. Sum and Avg • Sum (totals values for specific attribute) What was the total number of students that were allowed to register for winter 2000 classes? SELECT SUM(maxallowed) FROM section WHERE term = 'winter' AND year = '2000'; • Avg (finds average value of an attribute) What's the average salary of finance profs? SELECT AVG(salary) FROM Prof WHERE dept = 'fin'; List the average salary and total salary of all profs. SELECT AVG(salary),SUM(salary) FROM Prof;

  48. Chapter 10 Grouping data

  49. Categorizing Results • GROUP BY • groups output according to column value(s) • Allows operations on groups of rows List the average salary and total salary of all profs for each department. SELECT dept, AVG(salary),SUM(salary) FROM Prof; SELECT dept, AVG(salary),SUM(salary) FROM Prof GROUP BY dept;

  50. More on Group By’s… • How many students are in each major? SELECT COUNT(stud_id) FROM student GROUP BY major; SELECT COUNT(stud_id) , major FROM student GROUP BY major; • How many students are in each major by classification? SELECT COUNT(stud_id) , major, classification FROM student GROUP BY major; SELECT COUNT(stud_id) , major, classification FROM student GROUP BY major, classification; SELECT COUNT(stud_id) , major FROM student GROUP BY major, classification;

More Related