程序代写 CMT302 E-commerce & Innovation

CMT302 E-commerce & Innovation
LAB 2 (Session 1)
Dr Natasha Edwards Office: C/2.02

SQL: data types
Example types (there are several others):
INT ¡ª integer.
FLOAT ¡ª floating point number. VARCHAR(n) ¡ª string of max length n. TEXT ¡ª larger pieces of text.
DATE ¡ª date in format YYYY-MM-DD.
Can follow INT with AUTO INCREMENT in which case the value is set automatically and incremented whenever a new value is encountered (set the attribute to NULL when entering data with INSERT).
For more info about data types in SQL/MySQL:
http://dev.mysql.com/doc/refman/5.0/en/data-types.html

SQL: case sensitivity
MySQL commands are case-insensitive, but traditionally written in UPPER case:
e.g. CREATE TABLE, SELECT, INSERT, etc.
MySQL ¡¯identifiers¡¯ (names of databases, tables, index, columns, stored procedures, etc.) can be case sensitive, depending on your MySQL and/or OS configuration
e.g. see documentation for identifiers¡¯ case sensitivity here: https://dev.mysql.com/doc/refman/5.7/en/ identifier-case-sensitivity.html

SQL: creating tables
The command CREATE TABLE creates a table in a database. Example:
CREATE TABLE Branch (branchNo VARCHAR(10) NOT NULL, street VARCHAR(20) NOT NULL,
city VARCHAR(20) NOT NULL);
These fields declared as strings using the VARCHAR type.
VARCHAR(10) ¡ª maximum of 10 characters.
NOT NULL ¡ª must have value.

SQL: adding rows
To add a row (record) to a table, use the INSERT command. Example:
INSERT INTO Branch (branchNo, street, city) VALUES (“B008”, “45 Low St”, “Upborough”);
If all fields of a row are being inserted then we don¡¯t need to specify the list of fields, pro- vided they are listed in correct order.

SQL: changing values
To change a value of a field use the UPDATE command together with a condition (WHERE. . . ). Example:
UPDATE Branch SET city = “Downborough” WHERE Branch.branchNo =”B008″;
Various operators can be used in the WHERE clause:
Logical operators: AND, OR, NOT
Equivalence: ==, != Comparision: >, <, etc. SQL: deleting rows The DELETE command deletes entire rows. We can use conditions on which rows to delete. Example: DELETE FROM Branch WHERE branchNo = "B008"; Various operators can be used in the WHERE clause: All rows that satisfy the condition are deleted (only one in this example). Here the row to delete is the one where the branchNo is B008. SQL: retrieving data The SELECT command provides many ways to retrieve data. For example, find records from a table where an attribute has a particular value: SELECT branchNo, street, city FROM Branch WHERE city="Bristol"; SQL: retrieving data The WHERE clause of the SELECT command can take other conditions. For example, let us find sta with salary more than 20,000. Tip: to retrieve all fields use * for the list of fields. SELECT * FROM Staff WHERE salary > 20000;

Retrieving data: linking tables
To retrieve associated data from multiple tables they need to have at least one attribute (key) in common.
Which is a common attribute?

Retrieving data: a join between two tables
To retrieve related data that are in two tables use a common attribute (e.g. branchNo) to match records. This is called a join. Example:
SELECT S.firstName, S.lastName FROM Branch B, Staff S
WHERE S.branchNo = B.branchNo AND B.city = “London”;

SQL: example queries
Which persons work in London?
Are there any managers working in 163 Main St? Whose salary is greater than average?
Employees in which city have the highest salary? What is the lowest salary in Glasgow?

SQL: example queries (3)
Whose salary is greater than average?
SELECT firstName, lastName, salary FROM Staff WHERE salary > (SELECT AVG(SALARY) FROM Staff);
+———–+———-+——–+
| firstName | lastName | salary |
+———–+———-+——–+
+———–+———-+——–+
| 40000 |
| 25000 |
| 32000 |

SQL: example queries (4)
Employees in which city have the highest salary?
SELECT city, salary FROM Staff INNER JOIN Branch ON Staff.branchNo=Branch.branchNo
ORDER BY salary DESC LIMIT 1;
+——–+————-+
| city | MAX(salary) |
+——–+————-+
| London | 40000 |
+——–+————-+

SQL: example queries (5)
What is the lowest salary in Glasgow?
SELECT firstName, lastName, salary FROM
Staff INNER JOIN Branch ON Staff.branchNo=Branch.branchNo AND Branch.city=”Glasgow” ORDER BY salary ASC LIMIT 1;
+———–+———-+——–+
| firstName | lastName | salary |
+———–+———-+——–+
| Ann | Beech | 18000 |
+———–+———-+——–+

Way Forward
Resources and further reading:
MySQL documentation for download:
-> http://dev.mysql.com/doc/ MySQL tutorials on the Web, e.g.
-> https://dev.mysql.com/doc/refman/5.7/en/ tutorial.html
-> https://www.tutorialspoint.com/mysql/ SQL Quick Reference From W3Schools:
-> https://www.w3schools.com/sql/sql_quickref.asp Lot of database books in our library

程序代写 CS代考加微信: powcoder QQ: 1823890830 Email: powcoder@163.com

Related Posts