Database Testing - SQL - Insertion of Values in the Tables

To insert values in to these tables we use Data Manipulation Language Command called INSERT which allows us to insert values in to the columns of the table. Any string inserted should be enclosed within ''

Syntax:

Insert [tablename] value (col1, col2, coln)

To insert values in the Location table

Insert location
values (122, 'New York'),
(123, 'Dallas'),
(124, 'Chicago'),
(167, 'Boston')

To insert values in the department table

Insert department
values (10, 'Accounts', 122),
(20, 'Research', 124),
(30, 'Sales', 123),
(40, 'Operations', 167)

To insert values in the job table

Insert job
values (667, 'Clerk'),
(668, 'staff'),
(669, 'analyst'),
(670, 'salesperson'),
(671, 'manager'),
(672, 'president')

To insert values in the employee table

insert employee
values (7369,'Smith', 'John', 'Q', 667,7902,'17-Dec-84', 800,'Null' , 20)

insert employee
values (7499,'Allen', 'Kevin', 'J', 670,7698,'20-Feb-85', 1600, 300, 30)

insert employee
values (7505,'Doyle', 'Jean', 'K', 671,7839,'04-Apr-85', 2850, 'Null', 30)

insert employee
values (7506,'Dennis', 'Lynn', 'S', 671,7839,'15-May-85', 2750, 'Null', 30)

insert employee
values (7507,'Baker', 'Leslie', 'D', 671,7839,'10-Jun-85', 2200, 'Null', 40)

insert employee
values (7521,'Wark', 'Cynthia', 'D', 670,7698,'22-Feb-85', 1250, 500, 30)

Database testing - SQL - Creation of Database and Tables for practice

First of all let us create a database with tables so that we can use all the examples given in the next posts for practice and this will help us to have a hold on SQL.

To create a database we use the Data Definition Language Command called CREATE

Syntax:

Create database [Name of the database]

Example:

create database Practice

To create different tables we use the DDL command Create and for tables we need to use the keyword table. We also need to specify the column names and the data type the column holds

Syntax:

Create table [Name of the table]
(
Column 1 Datatype,
Column 2 Datatype,
Column n Datatype
)

Example:

To create a location table for our Practice Database

Create table location
(
location_id int,
regional_group varchar(100)
)

To create a department table for our Practice Database

Create table department
(
department_id int,
Name varchar(100),
location_id int
)

To create a job table for our Practice Database

Create table job
(job_id int,
function1 varchar(50)
)

To create a employee table for our Practice Database

Create table employee
(
employee_id int,
Last_name varchar(100),
First_name varchar(100),
Middle_name varchar(100),
Job_id int,
manager_id int,
hiredate datetime,
salary int,
comm varchar(100) ,
department_id int
)