1. Create a three-chapter table:
Student Information Form (stuInfo): Student ID (primary key), Name (cannot be blank), Gender (can only be male or female), Birthday
Course Information Table (subInfo): Number (primary key), Course name (cannot be empty)
Score Information Table (scoreInfo): Number (primary key), Student number (foreign key), Course number (foreign key), Score (between 0-100)
2. Insert data into three tables with no less than 5 rows
iii. Create a new table stu1 and insert the student number, name, and gender information of the student whose gender is female in the stuinfo into the table
4. Complete the following enquiries:
1. Query the information of all students with the surname Zhang (fuzzy query)
2. Query the information of all students over the age of 20 (implemented by date function)
3. Inquire about the information of students born on the same day (table link)
4. Check the names, subjects and results of all students who took the exam (in order of grades)
5. Check the student number and name of all students who have not taken the exam, as long as there is one course that has not been tested, it must be queried (sub-query)
6. Look up the name of the course without the student exam
7. The names and total scores of the students who took the exam were sorted from highest to smallest
8. Arrange all the scores in ascending order and generate serial numbers that are tied together without gaps
First, create a table as shown in the figure "Job 2 Data Rendering", the description of the table is as follows:
orders
orderID(order number) int primary key
orderDate dateTime
orderItem
itemID (item number) int primary key
orderID (the order corresponding to the int foreign key references the order number in the order table).
itemType(Product Category) varchar(20)
itemName varchar(20)
theNumber(product ordered quantity) int
theMoney int
1. Query the details of orders in Order 2 where the order unit price is larger than the average unit price
2. Query the order time is the order number, product category and product order quantity of the order one year ago (sub-query)
3. Query the quantity of the product type ordered in order 2 (the product category name is classified into one category)
4. Check the order number where the average unit price of the order is greater than the average unit price of order 1
5. Query the number, product category, product name, order quantity, order unit price, and order date of orders with order numbers 1 and 2 (table connection)
6. Query the order number of the non-order item (that is, the order number that exists in the order table but does not exist in the order item table, use sub-query)
7. Query the order number of the order item
|
Posted on 3/6/2015 2:38:44 PM
|
|
|
