QBE and SQL

1. QBE and SQL

2. QBE & SQL Examples Query By Example • SELECT Student.SName, Student.Major • FROM STUDENT • WHERE (((Student.Major)="ACC" Or (Student.Major)="MGT" Or (Student.Major)="MKT")); Structured Query Language • SELECT SName, Major • FROM Student • WHERE Major="ACC" Or Major="MGT" Or Major="MKT"; Can you see the difference?

3. QBE & SQL Examples Query By Example • SELECT tblLandlord.Surname, tblLandlord.Forename, [tblproperty book2].Street, [tblproperty book2].Postcode • FROM tblLandlord INNER JOIN [tblproperty book2] ON tblLandlord.Landcode = [tblproperty book2].Landcode; Structured Query Language • SELECT Surname, Forename, Street, Postcode • FROM tblLandlord INNER JOIN [tblproperty book2] ON tblLandlord.Landcode = [tblproperty book2].Landcode; Can you see the difference?

4. SQL from scratch Let us have a look at some simple SQL statements. To show all the fields from the Customers table SELECT * FROM Customers; To show all the Customers names beginning with P SELECT Forename,Surname from Customers where Surname Like ‘P*’; To show all the fields from the Customers table where surnames go from M to T SELECT * FROM Customers WHERE Surname Between 'M' And 'T';

5. All Columns, All Rows List full details of all staff. SELECT staffNo, fName, lName, address, position, sex, DOB, salary, branchNo FROM Staff; You can use * as an abbreviation for 'all columns': SELECT * FROM Staff;

6. All Columns, All Rows

7. Specific Columns, All Rows Produce a list of salaries for all staff, showing only staff number, first and last names, and salary. SELECT staffNo, fName, lName, salary FROM Staff;

8. Query View SQL SELECT Major, SName FROM Student; Table Fields QBE SELECT Student.Major, Student.SName FROM STUDENT;

9. SQL • Results would be same whichever method used for interrogating database Query view, SQL or QBE view

10. SELECT Major, SName FROM Student; • List all majors with students currently enrolled RESULTS Shows majors repeated

11. Use of DISTINCT List the property numbers of all properties that have been viewed. SELECT propertyNo FROM Viewing;

12. Use of DISTINCT Use DISTINCT to eliminate duplicates: SELECT DISTINCT propertyNo FROM Viewing;

13. Use of DISTINCT Produce a list showing the different types of property available in each city SELECT city, type FROM PropertyForRent;

14. Use of DISTINCT Use DISTINCT to eliminate duplicates SELECT DISTINCT city, type FROM PropertyForRent;

15. Calculated Fields Produce a list of monthly salaries for all staff, showing staff number, first and last names, and salary details. SELECT staffNo, fName, lName, salary/12 FROM Staff;

16. Don’t show the SName Position Cursor between columns so you get two way arrow Right Click and Select Properties from menu Change Unique Values from No to Yes • List all majors with students currently enrolled SQL SELECT DISTINCT Major FROM Student

17. List students, majors and GPAs sorted by name SELECT SName, Major, GPA FROM Student ORDER BY SName;

18. Ordering Results • ORDER BY – arranges data in Ascending order (default) • Need to add DESC for Descending order eg ORDER BY SName DESC • Columns can be ordered by one or more columns eg ORDER BY SName – Single column ordering eg ORDER BY SName DESC, Major – 2 column ordering first in Descending order of student name and then in Ascending order of Major

19. SQL Single Search Criteria Text SELECT SName, Major FROM Student WHERE Major = "ACC"; • List all students with Accounting majors Actual required criteria. If Text need to put it in speech marks Search Criteria field

20. SQL Single Search Criteria Number SELECT SName, GPA FROM Student WHERE GPA >=3.25 ORDER BY GPA DESC; • List all students with at least 3.25 GPA (grade pass average) Actual criteria required is a number therefore speech marks are not required. > Greater than < Less than >= Greater than and equal to < Less than and equal to = Equal to

21. SQL More than one search criteria meeting both parts of criteria SELECT SName, SGPA, Sex FROM Student WHERE GPA >=3.25 AND Sex = "F"; ORDER BY GPA DESC; • List women students with at least 3.25 GPA

22. SQL More than one search criteria meeting either criteria SELECT SName, Major, GPA FROM Student WHERE Major =("ACC" OR Major ="FIN“ ) AND GPA >=3.25 ORDER BY GPA DESC; • List students with accounting or finance majors exceeding 3.25 GPA

23. SQL Use of Between Criteria SELECT SName, GPA FROM Student WHERE GPA BETWEEN 2.4 AND 3.5 ORDER BY GPA DESC; • List students with GPAs between 2.4 and 3.5

24. NULL Search Condition List the STUDENTS that have not yet been given a GPA. Have to test for null explicitly using special keyword IS NULL: SELECT SID, SName FROM Student WHERE GPA IS NULL;

25. SQL Using OR criteria SELECT SName, Major FROM Student WHERE Major "ACC" OR Major ="MGT" OR Major ="MKT“; • List students in accounting, management or marketing Two ways of writing or query in Access

26. Pattern Matching • SQL has two special pattern matching symbols: • %: sequence of zero or more characters; • _ (underscore): any single character. • LIKE ‘Emily%’ means the first 5 characters must be Emily followed by anything.

27. SQL Pattern matching using Like SELECT SName FROM Student WHERE SName Like "P*"; • List all students whose name begins with “P” Access uses * as wild card other systems use the % symbol as the wild card First letter is fixed Wild card is used to replace string following fixed letter In this case arker, elnick, and oirer strings

28. SQL Pattern matching SELECT SName FROM Student WHERE SName Like "*ar*"; • List all the students whose last name contains “ar” Note the position and use of wild cards replacing strings before and after fixed “ar” Parker, Richards