Oracle Database SQL statement exercises-preparation

Oracle Database SQL statement exercises-preparation

Preface
I have been learning Oracle SQL statements for some time. I have always determined that the true learning is:
It can be used in practice. Otherwise, it can only be explained that it is simple to know something, but it is not really mastered. Front
Oracle functions, SQL syntax, and so on! Therefore, John
During a period of time, you will find some exercises to learn the syntax features of SQL. Check for missing items in the exercise.
However, we need to make some preparations before that, because this set of questions is obtained from the Internet and some new tables will be created.
Destroying the original structure of the Oracle database, John plans to create a new user kiritor for testing.
Create user
First, create a user, and then unlock the user [SQL] SQL> conn sys/kiritor as sysdba; -- log on as an administrator
SQL> create user kiritor identified by kiritor; -- create a user and specify the password
User created-after the user is successfully created, the User needs to be unlocked [SQL] SQL> alter user kiritor account unlock;
 
User altered -- Unlock successful
Grant User Permissions
After a user is created, the user must be granted relevant permissions to perform relevant operations. For example, the user must be granted a session
The create session permission user is used to log on to the oracle database. The permissions of Oracle users are complex.
John directly grants the administrator permissions. As for the user permissions in oralce, a detailed summary will be provided after time. [SQL] SQL> grant dba to kiritor;
 
Grant succeeded -- permission granted successfully. If you are interested in oracle user permissions, you can use the following statement to perform a simple query: [SQL] select distinct privilege from dba_sys_privs;

User Logon, table creation, and information entry [SQL] -- create a student information table
Drop table student;
Create table student
(
Sno varchar2 (10) primary key,
Sname varchar2 (20 ),
Sage number (2 ),
Ssex varchar2 (5)
);
-- Create a instructor table
Create table teacher
(
Tno varchar2 (20) primary key,
Tname varchar2 (20)
);
-- Create a curriculum
Create table course
(
Cno varchar2 (10 ),
Cname varchar2 (20 ),
Tno varchar2 (10 ),
Constraint pk_course primary key (cno, tno)
-- Constraint indicates a constraint, and primary key indicates a primary key constraint.
-- And the primary key column is a (cno, tno) combination Column
);
-- Create a score table
Create table score
(
Sno varchar2 (10 ),
Cno varchar2 (10 ),
Score number (4, 2 ),
Constraint pk_ SC primary key (sno, cno)
-- The Union primary key is defined using this method.
);
-- Query whether to create
Select * from cat;
------------- Initialize the data in the student table --------------------
Insert into student values ('s001 ', 'zhang san', 23, 'male ');
Insert into student values ('s002 ', 'lily', 23, 'male ');
Insert into student values ('s003 ', 'wu peng', 25, 'male ');
Insert into student values ('s004 ', 'wuqin', 20, 'female ');
Insert into student values ('s005 ', 'lily', 20, 'female ');
Insert into student values ('s006 ', 'ripbo', 21, 'male ');
Insert into student values ('s007 ', 'Liu Yu', 21, 'male ');
Insert into student values ('s008 ', 'xiao Rong', 21, 'female ');
Insert into student values ('s009', 'huang jie', 23, 'female ');
Insert into student values ('s010 ', 'chenmei', 22, 'female ');
Commit;
-------------- Initialize the instructor table ---------------------------
Insert into teacher values ('t001', 'liuyang ');
Insert into teacher values ('t002 ', 'wang yany ');
Insert into teacher values ('t003 ', 'hu Xiao Meng ');
Commit;
-------------- Initialize the course schedule ----------------------------
Insert into course values ('c001', 'j2se', 't002 ');
Insert into course values ('c002', 'java web', 't002 ');
Insert into course values ('c003 ', 'ssh', 't001 ');
Insert into course values ('c004 ', 'oracle', 't001 ');
Insert into course values ('c005 ',' SQL SERVER 100', 't003 ');
Insert into course values ('c006 ', 'c #', 't003 ');
Insert into course values ('c007 ', 'javascript', 't002 ');
Insert into course values ('c008 ', 'div + CSS', 't001 ');
Insert into course values ('c009', 'php', 't003 ');
Insert into course values ('c010', 'ejb3. 0', 't002 ');
Commit;
---------------- Initialize the orders table -------------------------
Insert into score values ('s001', 'c001', 78.9 );
Insert into score values ('s002 ', 'c001', 80.9 );
Insert into score values ('s003 ', 'c001', 81.9 );
Insert into score values ('s004 ', 'c001', 60.9 );
Insert into score values ('s001', 'c002', 82.9 );
Insert into score values ('s002 ', 'c002', 72.9 );
Insert into score values ('s003 ', 'c002', 81.9 );
Insert into score values ('s001 ', 'c003', '59 ');
Commit;
-----------------------------------------------
-- The Initialization is complete, and the preparation is complete. You can start related exercises later! First come here