Scripts you might need: UserCreation, TablesCreation.
If you are using LiveSQL: LiveSQL_1
Okay, we are finally here. In this lesson, you are going to create 2 simple tables that you will be using to test your SQL commands in the following lessons.
To create these tables you are going to have to run the scripts that are included at the top of this page, but before doing that, I will review them here, to give you a basic explanation of what they do.
Creating a new User
So, the first script you will need to run is the one called “UserCreation”. You only need to run this one if you are using your own database to take this course. If you are using the Live SQL site, you don’t need to run this script.
This is what it contains:
CREATE USER course IDENTIFIED BY course; -- User creation
GRANT CREATE SESSION to course; -- Necessary to connect to the database
GRANT CREATE ANY TABLE to course; -- Necessary to be able to create tables
GRANT RESOURCE to course; -- Necessary to extend the system tablespace
GRANT SELECT_CATALOG_ROLE to course; -- Necessary to be able to AutoTrace commands
GRANT CREATE ANY VIEW TO course; -- Necessary to be able to create views
GRANT UNLIMITED TABLESPACE TO course; -- Necessary to extend a tablespace when needed
If you are using your own database, you need to create a new user to create these test tables and to manipulate the information in the database in future lessons. This is necessary because, even though you have the sys account that could actually be used for that, the sys account should only be used for administrative purposes, because it is too powerful. It should only be used for database administration.
So, the user you will be creating is going to be called “course”, and its password, which is defined at the end of the first command, is also going to be “course”.
After creating the user account, you need to grant it some privileges needed for the things you will be doing with this new user. That is what the rest of the commands do.
Creating your Test Tables
Now, let’s look at the second script, which is called “TablesCreation”.
This script must be run with the new user you just created (course), you need to create a new database connection in SQL Developer using the “course” user, and run the tables creation script with that connection.
CREATE TABLE course.department
(
id number(5) constraint pk_department primary key,
name varchar2(50),
monthly_budget number(8,2),
last_employee_id number(5)
);
CREATE TABLE course.employee
(
id number(5) constraint pk_employee primary key,
name varchar2(50),
birthdate date,
phone varchar2(20),
salary number(7,2) not null,
department_id number(3) constraint fk_employee_department references course.department,
hire_date date,
job_id varchar2(20),
email varchar2(50),
bonus number(7,2)
);
insert into course.department values (1,'ACCOUNTING',20000,8);
insert into course.department values (2,'MARKETING',15000,9);
insert into course.department values (3,'INFORMATION TECHNOLOGY',30000,10);
insert into course.department values (4,'HUMAN RESOURCES',25000,13);
insert into course.department values (5,'REGULATORY AFFAIRS',5000,null);
insert into course.department values (6,'CUSTOMER SERVICE',2000,null);
insert into course.employee values (1,'JOHN SMITH',date '1995-01-01','1.123.456.7890',4000.00,1,date '2015-03-28','AC_ACCOUNT','JSMITH',null);
insert into course.employee values (2,'JAMES BOSH',date '1992-02-15','1.234.567.8901',3500.00,2,date '2014-07-01','MK_REP','JBOSH',null);
insert into course.employee values (3,'LUISA JACKSON',date '1970-03-08','1.345.678.9012',4500.00,3,date '2013-08-29','IT_PROG','LJACKSON',null);
insert into course.employee values (4,'STUART GARCIA',date '1965-04-12','1.456.789.0123',2000.00,4,date '2010-02-15','HR_REP','SGARCIA',null);
insert into course.employee values (5,'JUSTIN BLACK',date '1990-05-16','1.567.890.1234',2550.00,1,date '2015-05-02','AC_ACCOUNT','JBLACK',null);
insert into course.employee values (6,'ANGIE CROOD',date '1998-06-22','1.678.901.2345',1500.00,1,date '2015-07-01','AC_ACCOUNT','ACROOD',null);
insert into course.employee values (7,'CHARLES DEAN',date '1973-06-08','1.789.012.3456',2250.00,3,date '2002-03-01','IT_PROG','CDEAN',null);
insert into course.employee values (8,'EDDIE FARREL',date '1980-07-28','1.890.123.4567',3000.00,1,date '2009-04-20','AC_ACCOUNT','EFARREL',null);
insert into course.employee values (9,'GEORGE HAYES',date '1982-08-03',NULL,2500.00,2,date '2012-09-22','MK_REP','GHAYES',null);
insert into course.employee values (10,'IGOR KEYS OSBOURNE',date '1987-09-11','144.898.7564',6000.00,3,date '2014-11-14','IT_PROG','IKEYS',null);
insert into course.employee values (11,'LUKE MINT',date '1985-10-19','1.123.456.7890',5000.00,4,date '2011-01-08','HR_REP','LMINT',null);
insert into course.employee values (12,'NIGEL OAKS',date '1997-11-05','52.987.654.3210',4750.00,4,date '2014-10-01','HR_REP','NOAKS',null);
insert into course.employee values (13,'LUKE GREEN JR',date '1995-02-05',NULL,4750.00,4,date '2015-09-01','HR_REP','LGREEN',null);
commit;
The tables you will create are “Department” and “Employee”, and you will use the CREATE TABLE command, which is a DDL, or DATA DEFINITION LANGUAGE statement.
The department table has 4 columns, which are id, name, monthly budget, and last employee id. The employee table has more columns, which are id, name, birthdate, phone, salary, department id, hire date, job_id, email and bonus.
Both tables have a column called “id” which is the primary key in both of them, and the employee table has a foreign key, that references the id column in the department table.
You remember what primary and foreign keys are, right? We talked about them in the “Basic Database Concepts” lesson.
By now, don’t pay much attention to the data types of the columns, because we are going to talk about data types in the next lesson.
After creating the tables, you will insert some test data into them by means of the INSERT statement, which is also going to be covered in a future lesson.
And, at the end of the script you will confirm your transaction by means of the COMMIT statement, which is going to be covered down the road too.
So, now that you have an idea of what these scripts do, this is what you need to do:
- Scroll up to the top of the page, and download the appropriate scripts.
- Run the user creation script.
- Create a new database connection with the new user.
- Connect with your new database connection.
- And finally, run the tables creation script.
If you are using the Live SQL site you only need to do the final step.
Okay, please do this now, and when finished, continue to the next lesson.