SQL Commands for Data Modelling and Design | Desklib

Learn how to create tables, manipulate records, and use SQL commands like SELECT, UPDATE, INSERT, and DELETE. Get SQL queries for data modelling and design at Desklib.

Running head: DATA MODELLING AND DESIGN 1
Data Modelling and Design
Name of the Student
Institution Affiliation

DATA MODELLING AND DESIGN 2
Task 1
In order to create table using SQL commands we use the CREATE keyword followed by the
name of the table to be created and the attributes of the table are enclosed in brackets and
separated by commas, ( Rith, Lehmayr & Meyer, 2014). For foreign keys attribute, the attribute
should be referenced from the table where the foreign key is the primary key.
//Sql Command to create table SALARY
CREATE TABLE SALARY ( salary_level INT PRIMARY KEY, salarymin INT, salarymax
INT );
//SQL Command to create table Department
CREATE TABLE DEPARTMENT ( department_id int PRIMARY KEY , Deptname
VARCHAR(30), deptLocation VARCHAR(30), deptFloor VARCHAR(20) );
//SQL Command to create table EMPLOYEE
CREATE TABLE EMPLOYEE (
employee_id INT PRIMARY Key, EmpName VARCHAR2(25), Managerid INT, Dateofhire
Date, Jobname VARCHAR2(15), Salary DECIMAL(10,2), department_id INT, DOB
DATE, address VARCHAR(30), FOREIGN KEY (department_id) REFERENCES
department(department_id) );

DATA MODELLING AND DESIGN 3
Task 2
In order to add new records in a table, the INSERT keyword is used. The data to be added should
reflect the same way the columns are sequentially arranged. For strings, they must be enclosed in
a single or double quote unlink numeric which is not a must, (Neil, 2016).
//SQL Query to insert 10 records
INSERT INTO EMPLOYEE VALUES ('1', 'John Sale', '1111', '2017-07-02', 'Manager', 10000,
'11', '1980-05-03', 'Nax Vegas');
INSERT INTO EMPLOYEE VALUES ('2', 'Bill Johnson', '2222', '2017-08-02', 'Manager',
15000, '22', '1990-08-12', 'Castle daems');
INSERT INTO EMPLOYEE VALUES ('3', 'Cate Liz', '3333', '2017-08-12', 'Procurement',
84000, '33', '1987-12-17', 'MiddleTown Avenue');
INSERT INTO EMPLOYEE VALUES ('4', 'Clinton Davis', '4444', '2017-11-12', 'Human
resource', 92000, '44', '1970-12-01', 'CosmoTown');
INSERT INTO EMPLOYEE VALUES ('5', 'Robert', '5555', '2018-01-05', 'Security', 60000,
'5555', '1992-02-17', 'Middletown Avenue');
INSERT INTO EMPLOYEE VALUES ('6', 'Bream Gress', '1111', '2018-07-02', 'Casheir', 84000,
'11', '1985-05-03', 'Greate Gates');
INSERT INTO EMPLOYEE VALUES ('7', ' Flynn', '2222', '2018-08-02', 'Net Engineer',
144000, '22', '1985-08-12', 'River roads');
INSERT INTO EMPLOYEE VALUES ('8', 'Annah Lucile', '3333', '2018-08-12', 'Marketer',
75000, '33', '1988-06-17', 'Lavington Avenue');

DATA MODELLING AND DESIGN 4
INSERT INTO EMPLOYEE VALUES ('9', 'Hannah Joy', '4444', '2018-08-22', 'Secretary',
79000, '44', '1992-10-11', 'Middletown Streets');
INSERT INTO EMPLOYEE VALUES ('10', 'Martin Hurriz', '5555', '2019-01-05', 'Security',
65000, '5555', '1994-05-17', 'Hurringhum Avenue');
Task 3
//SQL query to insert 5 records in department table
INSERT INTO `department` (`department_id`, `Deptname`, `deptLocation`, `deptFloor`)
VALUES
('11', 'Accounts', 'Green-Estate', '5th Floor'),
('22', 'Technical', 'Eng Plaza', '4th Floor'),
('33', 'Procurement', 'Market Plaza', '3rd Floor'),
('44', 'Human Resource', 'HR Lane', '2nd Floor'),
('55', 'Surbodinates', 'SecuPlaza', '1st Floor')
Task 4
//SQL query to insert 5 records into salary table
INSERT INTO `salary` (`salary_level`, `salarymin`, `salarymax`) VALUES

DATA MODELLING AND DESIGN 5
(1', 50000, 70000), (2, 70001, 90000), (3, 90001, 110000), (4, 110001, 130000), (5, 130001,
160000);
Task 5
To display records from a table, the SELECT keyword command is used. To display all the
records, the asterisk(*) symbol is used, (Neil, 2016).
//SQL query to display all the records in EMPLOYEE table
SELECT * FROM `employee`
Task 6
To display specific records, the column names are mention separated by a comma(,), (Neil,
2016)
//SQL Query to display name of all employees
SELECT `EmpName` FROM `employee`
Task 7
// SQL query to display employee name and job name
SELECT `EmpName`, `Jobname` FROM `employee`

DATA MODELLING AND DESIGN 6
Task 8
According to Rockoff (2016), to display unique records from a table, the DISTICT keyword is
used followed by the column name where the unique records should be evaluated.
//SQL Query to display unique jobname for all employees
SELECT DISTINCT `Jobname` from employee
Task 9
To manipulate data in a table the keyword UPDATE … SET are used
//SQL Query to increase the salary of all employees by 12% and display Employee name, Job
name and salary
UPDATE `employee` SET `Salary`=`Salary`+(`Salary`*12/100);
SELECT `EmpName`,`Jobname`,`Salary`FROM employee
Task 10
To display the maximum and minimum records in a database, the keyword MAX and MIN are
used respectively and to join the statement so that the records can be displayed as one, the
UNION keyword is Used, (Neil, 2016)
//SQL Query to display employees’ names with minimum and maximum salary

DATA MODELLING AND DESIGN 7
SELECT `EmpName`, `Salary` FROM employee WHERE `Salary` = (select max(`Salary`)
from employee) UNION ALL SELECT `EmpName`, `Salary` FROM employee WHERE
`Salary` = (SELECT min(`Salary`) from employee);
Task 11
To display records whose value is greater than a certain value, the greater than sign(>) is used.
//SQL Query to display Employees Id Employee name, job name of all employees whose salary is
greator than 90000 pa
SELECT `employee_id`,`EmpName`,`Jobname` FROM `employee` WHERE `Salary`>90000
Task 12
//SQL Query to display employee details of employee whose job group is manager
SELECT * FROM `employee` WHERE `Jobname`="Manager";
Task 13
//SQL Query to display details of employee whose name is Robert
SELECT * FROM `employee` WHERE `EmpName`="Robert";
Task 14

DATA MODELLING AND DESIGN 8
To display records with two conditions, the AND keyword is used to join the two condition, thus
the records that will be displayed must be true in both conditions, (Lawrence, 2014).
//SQL Query to display Employees detail who work as manager and their salary is above 95000
SELECT * FROM `employee` WHERE `Jobname`="Manager" and `Salary`>95000;
Task 15
In order to extract year from the DATE data type the YEAR(Column name) is used.
// SQL Query to display employee id, name and job name of employees who joined later after
year 2001
SELECT `employee_id`,`EmpName`,`Jobname` FROM `employee` WHERE
YEAR(`Dateofhire`)>2001
Task 16
To display values that are in a certain range of values, the BETWEEN keyword is used,
(Rockoff, 2016).
//SQL Query to display employee records whose salary is between 55000 and 95000
SELECT * FROM `employee` WHERE `Salary` BETWEEN 55000 AND 95000
Task 17

DATA MODELLING AND DESIGN 9
To display records is a specific order(Ascending or Descending), the ORDER BY keyword
followed by the respective acronym is used, i.e DESC for descending
//SQL Query to display list of employees in descending order of their salary
SELECT * FROM `employee` ORDER by `Salary` DESC
Task 18
The count Keyword is used to count records in a table.
//SQL Query to count records in employee table
SELECT COUNT(`EmpName`) FROM employee
Table 19
//SQL Query to add employee with ANALYST as jobname
INSERT INTO EMPLOYEE VALUES ('113', 'Rubicon', '2222', '2013-07-02', 'ANALYST',
189000, '11', '1971-05-03', 'Joysters');
Task 20
//SQL Query to insert a new record

DATA MODELLING AND DESIGN 10
INSERT INTO `employee` (`employee_id`, `EmpName`, `Managerid`, `Dateofhire`, `Jobname`,
`Salary`, `department_id`, ) VALUES ('1011', 'Janet', '5095', '2014-10-12', 'PROGRAMMER',
'90000', '2011');
Task 21
To remove records in a table, the DELETE keyword is used, (Neil, 2016).
//SQL Query to delete records of employee whose name is “Flynn”
DELETE FROM `employee` WHERE `EmpName`="Flynn"
Task 22
//SQL Query to update salary of Employee whose name is Robert by 15%
UPDATE `employee` SET `Salary`=`Salary`+(`Salary`*15/100) WHERE EmpName`="Robert";
Task 23
To display records of the same type and the total sum, the SUM and GROUP By keywords are
used
//SQL Query to find number of staff working in each department and their salary
SELECT department_id, SUM(`Salary`) FROM employee GROUP BY `department_id`;

DATA MODELLING AND DESIGN 11
Task 24
To display records that contains specific words/string, the LIKE keyword is used, the word
should also be embedded in percentage symbol(%), (Rockoff, 2016).
//SQL Query to find employees with string “Avenues” in their Address
SELECT * FROM `employee` WHERE `address` LIKE "%Avenue%"
Summary of experience
I have learnt how to create tables, and the relevant constraints and manipulate the records in a
database. I have broadly learned how to use the four main SQL keyword; SELECT which is used
in displaying records from a table, UPDATE keyword which is used to update record(s) in a
table, INSERT keyword which is used to add new records into a table and DELETE keyword
which is used to remove records from a table. I have also learnt how to display specific records
such as orders containing certain strings, the sum of specific records, sorting records in
ascending order and displaying records in a specific range of value.

DATA MODELLING AND DESIGN 12
Reference
Lawrence, R. (2014, March). Integration and virtualization of relational SQL and NoSQL
systems including MySQL and MongoDB. In 2014 International Conference on
Computational Science and Computational Intelligence (Vol. 1, pp. 285-290). IEEE.
Nield, T. (2016). Getting Started with SQL: A Hands-on Approach for Beginners. " O'Reilly
Media, Inc.".
Rith, J., Lehmayr, P. S., & Meyer-Wegener, K. (2014, March). Speaking in tongues: SQL access
to NoSQL systems. In Proceedings of the 29th Annual ACM Symposium on Applied
Computing (pp. 855-857). ACM.
Rockoff, L. (2016). The language of SQL. Addison-Wesley Professional.