3185701
Description
Flashcards by nurhidayah.mazni, updated more than 1 year ago
|
Created by nurhidayah.mazni
over 8 years ago
|
|
| Question | Answer |
| Introduction to Structured Query Language (SQL) | # Similar to programming, database structure and manipulation will have to be coded in certain language in order to be executed by computers. #For relational database, the language is called Structured Query Language (SQL). #SQL is supported by many relational database software (with slight variation) such as Oracle, MySQL, SQL server, Microsoft Access etc. |
| S.Q.L | * SQL is relatively easy to learn Basic command set has vocabulary of less than 100 words. *Nonprocedural language American National Standards Institute (ANSI) prescribes a standard SQL. *Several SQL dialects exist. |
| SQL Data Definition Commands | |
| SQL Data Definition Commands (continued) | |
| SQL Data Definition Commands (continued) | |
| SQL Statements | |
| SELECT Statement | ** The purpose of SELECT statement is to retrieve and display data from one or more tables |
| Example : SELECT Statement | |
| Projection (π) | **Example 5.1 All Columns, All Rows -List full details of all staff ** πstaffNo, fName, lName, address, position, sex, DOB, salary, branchNo (STAFF). **SELECT staffNo, fName, lName, address, position, sex, DOB, salary, branchNo FROM Staff; -Can use * as an abbreviation for ‘all columns’: **SELECT * FROM Staff; |
| Projection (π) continued.. | |
| Selection (σ) | |
| Selection (σ) continued... | |
| SELECT Statement - Aggregates | - ISO standard defines five aggregate functions: i) COUNT returns number of values in specified column. ii) SUM returns sum of values in specified column. iii) AVG returns average of values in specified column. iv) MIN returns smallest value in specified column. iiv) MAX returns largest value in specified column. **Each operates on a single column of a table and returns a single value. |
| SELECT Statement - Aggregates (continued) | a) COUNT, MIN, and MAX apply to numeric and non-numeric fields, but SUM and AVG may be used on numeric fields only. b) Apart from COUNT(*), each function eliminates nulls first and operates only on remaining non-null values. c) COUNT(*) counts all rows of a table, regardless of whether nulls or duplicate values occur. d) Can use DISTINCT before column name to eliminate duplicates. e) DISTINCT has no effect with MIN/MAX, but may have with SUM/AVG. |
| Example: Use of COUNT(*) | |
| Example : Use of COUNT and SUM | |
| Example : Use of MIN, MAX, AVG | |
| Example : Use of HAVING |
Want to create your own Flashcards for free with GoConqr? Learn more.