Send the link below via email or IMCopy
Present to your audienceStart remote presentation
- Invited audience members will follow you as you navigate and present
- People invited to a presentation do not need a Prezi account
- This link expires 10 minutes after you close the presentation
- A maximum of 30 users can follow your presentation
- Learn more about this feature in our knowledge base article
The Relational Model 2: SQL
Transcript of The Relational Model 2: SQL
EXAMPLE 5: Find the name of customer 148
EXAMPLE 3: List the complete Part table.
EXAMPLE 7: List the number, name, credit limit, and balance for all customers with credit limits that exceed their balances.
EXAMPLE 4: List the name of every customer with a $10,000 credit limit
EXAMPLE 2: List the number, name and balance of all customers
EXAMPLE 6: Find the customer name for every customer located in the city of Grove
Formed by connecting two or more conditions using one or both the following operators: AND and OR.
It is the inner query and is evaluated first before the outer query can be evaluated.
Creating groups of records that share common characteristics.
GROUP BY clause – indicates grouping in SQL.
ORDER BY clause – sorts the query results.
- applies to group.
- COUNT(*) applies to groups.
WHERE clause – applies to rows only.
In Access SQL, the asterisk (*) is used as a wildcard to represent any collection of characters.
To use a wildcard, include the LIKE operator in the WHERE clause.
USING SPECIAL OPERATORS (LIKE AND IN)
Another wildcard is the question mark (?), which represents any individual character.
“T?m” = Tim, Tom or T3m.
Another operator, IN, provides a concise way of phrasing certain conditions.
The field on which to sort data is called a sort key. In SQL, you sort data using the ORDER BY clause.
When you need to sort data on two fields, the more important sort key is called the major sort key and the less important sort key is called the minor sort key
SQL has built in functions, also called aggregate functions, to calculate the number of entries, the sum or average of all entries in a given column, and the largest or smallest values in a given column. In SQL, these functions are called COUNT, SUM, AVG, MAX, and MIN respectively.
It is also possible to join more than two tables. For each pair of tables to join, you must include a condition indicating how the tables are related.
Example: For every order, list the order number, order date, customer number, and customer name. In addition, for each order line within the order, list the part number, description, number ordered, and quoted price. Order the records by order number.
Thus, you need to join four tables: Orders, Customer, Part, and Order Line.
The query results appear below.
The union of two tables is a table containing all rows that are in the first table, the second table, or both tables. The two tables involved in a union must have the same structure, or be union compatible.
Example: List the number and name of all customers that are represented by sales rep 35 or that currently have orders n file or both.
The query results appear below.
There are more uses for SQL than simply retrieving data from a database and creating tables. SQL has several other capabilities like updating tables.
Example: Change the street address of customer 524 to 1445 Rivard.
You can use the SQL UPDATE command to make changes to existing data.
Example: Add a new sales rep to the Rep table. Her number is 16; her name is Sharon Rands; and her address is 826 Raymond,
Altonville, FL 32543. She has not yet earned any commission, but her commission rate is 5% (0.05).
To add new data to a table, you use the INSERT command.
Example: Delete any row in the OrderLine table in which the part number is BV06.
To delete data from the database, use the DELETE command,
Lyza Loius Paet
SQL (Structured Query Language)
SQL provides users with the capability of querying a relational database. However, in SQL, you must enter commands to obtain the desired results rather than complete an on-screen form as you do in access and QBE.
What is SQL
SQL was developed under the name SEQUEL at the IBM San Jose research facilities as the data manipulation language for IBM’s prototype relational DBSM, System R, in the mid-1970’s.
in 1980- it was renamed SQL
SQL is the standard language for relational database manipulation.
In studying SQL, we will begin it by:
Examining how to use it to create a table.
Examining simple retrieval method and
Compute fields in SQLand learn how to sort data.
Learn how to use built-in functions, subqueries, and grouping.
Learn how to join tables and use the UNION operator.
Use SQL to update data in database.
GETTING STARTED WITH SQL
Using Access 2007 or 2010 and the Premiere Products database provided with the Data Files for this text, the table have already been created.
GETTING STARTED WITH ACCESS 2007and 2010
Open the Premiere Products database
Click the Create tab on the Ribbon
Click the Query Design button in the Other group in Access 2007 or in the Queries group in Access 2010
Click the Close button in the Show table dialog box
Click the View button arrow in the Results group on the Query Design Tools tab
Click SQL View
To execute SQL commands shown in the figures in Access 2007 or 2010. Do the following:
To run the SQL command:
-click the Run button in the Results group on the Query Tools Design tab
To return SQL view:
-click the View button arrow in the Views group on the Home tab, and then click SQL view.
CURRENCY- data type for fields that contain currency values.
NUMBER- data type for all numeric fields.
GETTING STARTED WITH MySQL
If you use the MySQL-Premiere script provided with the Data Files for this text to create and activate the Premiere (Products) database, the script will activate the database, create the tables, and insert the records for you.
To run a script in MySQL, type the SOURCE command followed by the name of the file, then press the Enter key.
Script name: MySQL-Premiere
Type the command:
To activate the database, type the USE command followed by the name of the database.
Ex. To activate the Premiere Products database.
Type the command:
You can edit the command in the statement history by using the editing keys.
Some common restrictions placed on the table and column names by DBMSs are as follows:
- The names cannot exceed 18 characters.
- The names must start with a letter.
- The names can contain only letters, numbers, and underscores (_).
- The names cannot contain spaces.
List of data types that you will often encounter: