3. Create store schema for Oracle database SQL development
Welcome to reprint, reprint please indicate the source: blog.csdn.net/notbaron/ar…
1. Create the Store mode
Create scripts in the source code, address download.csdn.net/detail/notb…
The script name is store_schema.sql
Connect through sqlplus/as sysdba
perform
SQL>@store_schema.sql
The store user will be created with the password store_password
You can also use SQL_DEVELPER to create a connection, use the SYS user to connect, and then execute the code in store_schema. SQL. The following
Figure 1
It took 9.966 seconds to complete.
You may need to grant more privileges, such as dba privileges to stores.
GRANTconnect, resource,dba TO store;
Then modify the date as follows:
SQL>show parameter nls_date_language;
In the CONNECT store/store_password;
Insert the following after this line of code:
altersession set nls_date_language=’AMERICAN’;
2. Store_schema. SQL statement description
Create user commands
CREATEUSER store IDENTIFIED BY store_password;
authorization
GRANTconnect, resource TO store;
Table 2.1
There are six main tables
L Customer details
L Type of products sold
L Product details
L Customer purchase history
L Employee information
L Salary grade
The table names are: Customers, product_types, Products, daylight robbery, employees, and Salary_grades
2.1.1 The statement is as follows
2.1.2 Customers
Store customer details,
View customers as follows:
SQL> desc customers;
Name Null? Type
————————————————- —————————-
CUSTOMER_ID NOT NULL NUMBER(38)
FIRST_NAME NOT NULL VARCHAR2(10)
LAST_NAME NOT NULL VARCHAR2(10)
DOB DATE
PHONE VARCHAR2(12)
First name, last name, birthday, phone number, user ID.
Create statement as follows:
CREATE TABLE customers (
customer_id INTEGER CONSTRAINT customers_pk PRIMARY KEY,
first_nameVARCHAR2(10) NOT NULL,
last_name VARCHAR2(10) NOT NULL,
dobDATE,
phone VARCHAR2(12)
);
The user ID, customer_ID is a unique integer. One or more columns in a table represent each row in the table. Such columns are called PRIMARY keys. CONSTRAINT stands for CONSTRAINT, also called customers_PK
Dob storage client birthdays can be empty.
View the table as follows:
SQL> select * from customers;
CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE
———– ———- ———- ———————
1 John Brown 01-JAN-65 800-555-1211
2 Cynthia Green 05-FEB-68 800-555-1212
3 Steve White 16-MAR-71 800-555-1213
4 Gail Black 800-555-1214
5 Doreen Blue 20-MAY-70
2.1.3 product_types
Type of product sold
Create the following command:
CREATE TABLE product_types (
product_type_id INTEGER CONSTRAINT product_types_pk PRIMARY KEY,
name VARCHAR2(10) NOT NULL
);
Where product_type_id is the primary key and has a unique integer value.
Name Contains the Name of the product type.
The query result is as follows:
SQL>select * from product_types;
PRODUCT_TYPE_ID NAME
————— ———-
1 Book
2 Video
3 DVD
4 CD
5 Magazine
2.1.4 products
Product details.
Storage product name, description, and price.
Create the following command:
CREATE TABLE products (
product_id INTEGER CONSTRAINT products_pk PRIMARY KEY,
product_type_id INTEGER
CONSTRAINT products_fk_product_types
REFERENCES product_types(product_type_id),
name VARCHAR2(30) NOT NULL,
description VARCHAR2(50),
price NUMBER(5, 2)
);
Where product_id is the primary key
Product_type_id associates each product with a product type. This column is a reference to the PRODUCt_type_id column in the PRODUCt_types table and is called a foreign key because columns from other tables are referenced. The products table that contains this foreign key is called a detail table or child table. The referenced table product_types is called the master table or the parent table. This kind of master-subordinate or father-child relationship. When adding a new product, you should provide a matching product_types.product_type_id value in the product_type_id column.
Check as follows:
SQL> select * from products;
PRODUCT_ID PRODUCT_TYPE_ID NAME
———- ———————————————
DESCRIPTION PRICE
————————————————————
1 1 Modern Science
A description of modern science 19.95
2 1 Chemistry
Introduction to Chemistry 30
3 2 Supernova
A star explodes 25.99
PRODUCT_ID PRODUCT_TYPE_ID NAME
———- ———————————————
DESCRIPTION PRICE
————————————————————
4 2 Tank War
Action movie about a future war 13.95
5 2 Z Files
Series on mysterious activities 49.99
6 2 2412: The Return
Aliens return 14.95
PRODUCT_ID PRODUCT_TYPE_ID NAME
———- ———————————————
DESCRIPTION PRICE
————————————————————
7 3 Space Force 9
Adventures of heroes is 13.49
8 3 From Another Planet
Alien from another planet lands on Earth 12.99
9 4 Classical Music
The best classical music 10.99
PRODUCT_ID PRODUCT_TYPE_ID NAME
———- ———————————————
DESCRIPTION PRICE
————————————————————
10 4 Pop 3
The best popular music 15.99
11 4 Creative Yell
Debut album 14.99
12 My Front Line
Their greatest hits 13.49
12 rows selected.
Where product_type_id is 1, it indicates that the product is a book. The Product_type_id value comes from the product_types table.
2.1.5 purchases
Customer purchase history. Need to store:
Product ID, customer ID, number of products purchased by that customer.
Create the following command:
CREATE TABLE purchases (
product_id INTEGER
CONSTRAINT purchases_fk_products
REFERENCES products(product_id),
customer_id INTEGER
CONSTRAINT purchases_fk_customers
REFERENCES customers(customer_id),
quantity INTEGER NOT NULL,
CONSTRAINT purchases_pk PRIMARY KEY (product_id, customer_id)
);
Product_id stores the ID of the purchased product and must match the value of the product_id column in a row in the Products table.
Customer_id stores the customer ID that purchased the product and must match the value of the Customer_id column in a row in the Customers table
Quantity Stores the Quantity of products purchased.
There is a primary key constraint named Purchases_PK that spans two columns in the table: Product_id and Customer_id. The combination of the two column values must be unique for each row record in the table. When a primary key consists of multiple columns, it is called a compound primary key.
Check as follows:
SQL> select * from purchases;
PRODUCT_ID CUSTOMER_ID QUANTITY
———- ———– ———-
1 1 1
1 2 3
4 1
2 2 1
3 1
1 2 2
2, 3, 1
2 4 1
3 3 1
9 rows selected.
2.1.6 employees
Employee information, including the following contents: Employee ID, if there is a superior manager store superior manager employee, first name, last name, position, salary.
Create the following command:
CREATE TABLE employees (
employee_id INTEGER CONSTRAINT employees_pk PRIMARY KEY,
manager_idINTEGER,
first_name VARCHAR2(10) NOT NULL,
last_name VARCHAR2(10) NOT NULL,
title VARCHAR2(20),
salary NUMBER(6, 0)
);
Query as follows:
SQL>select * from employees;
EMPLOYEE_ID MANAGER_ID FIRST_NAMELAST_NAME TITLE SALARY
———– ———- ——————– ——————– ———-
1 James Smith CEO 800000
2 1 Ron Johnson Sales Manager 600000
3 2 Fred Hobbs Salesperson 150000
4 2 Susan Jones Salesperson 500000
2.1.7 salary_grades
The salary level contains the following contents: Salary level ID, minimum salary of the salary level, and maximum salary of the salary level.
Create the following command:
CREATE TABLE salary_grades (
salary_grade_id INTEGER CONSTRAINT salary_grade_pk PRIMARY KEY,
low_salary NUMBER(6, 0),
high_salary NUMBER(6, 0)
);
Query as follows:
SQL>select * from salary_grades;
SALARY_GRADE_ID LOW_SALARY HIGH_SALARY
————— ———- ———–
1, 1, 250000
2 250001 500000
3 500001 750000
4, 750001, 999999