Scribd
Upload
21 views21 pages

SQL Database Creation and Management Guide

The document provides a comprehensive guide on SQL commands for creating and managing databases, including creating tables, inserting data, updating and deleting records, and executing basic queries. It also covers advanced topics such as constraints, joins, nested queries, and triggers. Additionally, it includes examples for a company database with employee and client management, showcasing various SQL functionalities.

Uploaded by

israelmichael2019
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
21 views21 pages

SQL Database Creation and Management Guide

The document provides a comprehensive guide on SQL commands for creating and managing databases, including creating tables, inserting data, updating and deleting records, and executing basic queries. It also covers advanced topics such as constraints, joins, nested queries, and triggers. Additionally, it includes examples for a company database with employee and client management, showcasing various SQL functionalities.

Uploaded by

israelmichael2019
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

CREATING TABLES

Code

INT -- Whole Numbers

DECIMAL(M,N) -- Decimal Numbers - Exact Value

VARCHAR(l) -- String of text of length l

BLOB -- Binary Large Object, Stores large data

DATE -- 'YYYY-MM-DD'

TIMESTAMP -- 'YYYY-MM-DD HH:MM:SS' - used for recording events

-- Creating tables

CREATE TABLE student (

student_id INT PRIMARY KEY,

name VARCHAR(40),

major VARCHAR(40)

-- PRIMARY KEY(student_id)

);

DESCRIBE student;

DROP TABLE student;

ALTER TABLE student ADD gpa DECIMAL;

ALTER TABLE student DROP COLUMN gpa;

INSERTING DATA

INSERT INTO student VALUES(1, 'Jack', 'Biology');

INSERT INTO student VALUES(2, 'Kate', 'Sociology');


INSERT INTO student(student_id, name) VALUES(3, 'Claire');

INSERT INTO student VALUES(4, 'Jack', 'Biology');

INSERT INTO student VALUES(5, 'Computer Science');

CONTRAINTS

CREATE TABLE student (

student_id INT PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(40) NOT NULL,

-- name VARCHAR(40) UNIQUE,

major VARCHAR(40) DEFAULT 'undecided',

);

UPDATE AND DELETE

DELETE FROM student;

DELETE FROM student

WHERE student_id = 4;

DELETE FROM student

WHERE major = 'Sociology' AND name = 'Kate';

UPDATE student

SET major = 'Undecided';

UPDATE student

SET name = 'Johnny'


WHERE student_id = 4;

UPDATE student

SET major = 'Biological Sciences'

WHERE major = 'Biology';

UPDATE student

SET major = 'Biosociology'

WHERE major = 'Biology' OR major = 'sociology'

UPDATE student

SET major = 'Undecided', name = 'Tom'

WHERE student_id = 4;

BASIC QUERIES

SELECT *

FROM student;

SELECT [Link], [Link]

FROM student;

SELECT *

FROM student

WHERE name = 'Jack';


SELECT *

FROM student

WHERE student_id > 2;

SELECT *

SELECT *

FROM student;

SELECT [Link], [Link]

FROM student;

SELECT *

FROM student

WHERE name = 'Jack';

SELECT *

FROM student

WHERE student_id > 2;

SELECT *

FROM student

WHERE major = 'Biology'

CREATING COMPANY DATABASE

CREATE TABLE employee (


emp_id INT PRIMARY KEY,

first_name VARCHAR(40),

last_name VARCHAR(40),

birth_day DATE,

sex VARCHAR(1),

salary INT,

super_id INT,

branch_id INT

);

CREATE TABLE branch (

branch_id INT PRIMARY KEY,

branch_name VARCHAR(40),

mgr_id INT,

mgr_start_date DATE,

FOREIGN KEY(mgr_id) REFERENCES employee(emp_id) ON DELETE SET NULL

);

ALTER TABLE employee

ADD FOREIGN KEY(branch_id)

REFERENCES branch(branch_id)

ON DELETE SET NULL;

ALTER TABLE employee

ADD FOREIGN KEY(super_id)


REFERENCES employee(emp_id)

ON DELETE SET NULL;

CREATE TABLE client (

client_id INT PRIMARY KEY,

client_name VARCHAR(40),

branch_id INT,

FOREIGN KEY(branch_id) REFERENCES branch(branch_id) ON DELETE SET NULL

);

CREATE TABLE works_with (

emp_id INT,

client_id INT,

total_sales INT,

PRIMARY KEY(emp_id, client_id),

FOREIGN KEY(emp_id) REFERENCES employee(emp_id) ON DELETE CASCADE,

FOREIGN KEY(client_id) REFERENCES client(client_id) ON DELETE CASCADE

);

CREATE TABLE branch_supplier (

branch_id INT,

supplier_name VARCHAR(40),

supply_type VARCHAR(40),

PRIMARY KEY(branch_id, supplier_name),

FOREIGN KEY(branch_id) REFERENCES branch(branch_id) ON DELETE CASCADE


);

-- -----------------------------------------------------------------------------

-- Corporate

INSERT INTO employee VALUES(100, 'David', 'Wallace', '1967-11-17', 'M', 250000, NULL, NULL);

INSERT INTO branch VALUES(1, 'Corporate', 100, '2006-02-09');

UPDATE employee

SET branch_id = 1

WHERE emp_id = 100;

INSERT INTO employee VALUES(101, 'Jan', 'Levinson', '1961-05-11', 'F', 110000, 100, 1);

-- Scranton

INSERT INTO employee VALUES(102, 'Michael', 'Scott', '1964-03-15', 'M', 75000, 100, NULL);

INSERT INTO branch VALUES(2, 'Scranton', 102, '1992-04-06');

UPDATE employee

SET branch_id = 2

WHERE emp_id = 102;


INSERT INTO employee VALUES(103, 'Angela', 'Martin', '1971-06-25', 'F', 63000, 102, 2);

INSERT INTO employee VALUES(104, 'Kelly', 'Kapoor', '1980-02-05', 'F', 55000, 102, 2);

INSERT INTO employee VALUES(105, 'Stanley', 'Hudson', '1958-02-19', 'M', 69000, 102, 2);

-- Stamford

INSERT INTO employee VALUES(106, 'Josh', 'Porter', '1969-09-05', 'M', 78000, 100, NULL);

INSERT INTO branch VALUES(3, 'Stamford', 106, '1998-02-13');

UPDATE employee

SET branch_id = 3

WHERE emp_id = 106;

INSERT INTO employee VALUES(107, 'Andy', 'Bernard', '1973-07-22', 'M', 65000, 106, 3);

INSERT INTO employee VALUES(108, 'Jim', 'Halpert', '1978-10-01', 'M', 71000, 106, 3);

-- BRANCH SUPPLIER

INSERT INTO branch_supplier VALUES(2, 'Hammer Mill', 'Paper');

INSERT INTO branch_supplier VALUES(2, 'Uni-ball', 'Writing Utensils');

INSERT INTO branch_supplier VALUES(3, 'Patriot Paper', 'Paper');

INSERT INTO branch_supplier VALUES(2, 'J.T. Forms & Labels', 'Custom Forms');

INSERT INTO branch_supplier VALUES(3, 'Uni-ball', 'Writing Utensils');

INSERT INTO branch_supplier VALUES(3, 'Hammer Mill', 'Paper');

INSERT INTO branch_supplier VALUES(3, 'Stamford Lables', 'Custom Forms');


-- CLIENT

INSERT INTO client VALUES(400, 'Dunmore Highschool', 2);

INSERT INTO client VALUES(401, 'Lackawana Country', 2);

INSERT INTO client VALUES(402, 'FedEx', 3);

INSERT INTO client VALUES(403, 'John Daly Law, LLC', 3);

INSERT INTO client VALUES(404, 'Scranton Whitepages', 2);

INSERT INTO client VALUES(405, 'Times Newspaper', 3);

INSERT INTO client VALUES(406, 'FedEx', 2);

-- WORKS_WITH

INSERT INTO works_with VALUES(105, 400, 55000);

INSERT INTO works_with VALUES(102, 401, 267000);

INSERT INTO works_with VALUES(108, 402, 22500);

INSERT INTO works_with VALUES(107, 403, 5000);

INSERT INTO works_with VALUES(108, 403, 12000);

INSERT INTO works_with VALUES(105, 404, 33000);

INSERT INTO works_with VALUES(107, 405, 26000);

INSERT INTO works_with VALUES(102, 406, 15000);

INSERT INTO works_with VALUES(105, 406, 130000);

MORE BASIC QUERIES

-- Find all employees

SELECT *

FROM employee;
-- Find all clients

SELECT *

FROM clients;

-- Find all employees ordered by salary

SELECT *

from employee

ORDER BY salary ASC/DESC;

-- Find all employees ordered by sex then name

SELECT *

from employee

ORDER BY sex, name;

-- Find the first 5 employees in the table

SELECT *

from employee

LIMIT 5;

-- Find the first and last names of all employees

SELECT first_name, employee.last_name

FROM employee;

-- Find the forename and surnames names of all employees


SELECT first_name AS forename, employee.last_name AS surname

FROM employee;

-- Find out all the different genders

SELECT DISCINCT sex

FROM employee;

-- Find all male employees

SELECT *

FROM employee

WHERE sex = 'M';

-- Find all employees at branch 2

SELECT *

FROM employee

WHERE branch_id = 2;

-- Find all employee's id's and names who were born after 1969

SELECT emp_id, first_name, last_name

FROM employee

WHERE birth_day >= 1970-01-01;

-- Find all female employees at branch 2

SELECT *

FROM employee
WHERE branch_id = 2 AND sex = 'F';

-- Find all employees who are female & born after 1969 or who make over 80000

SELECT *

FROM employee

WHERE (birth_day >= '1970-01-01' AND sex = 'F') OR salary > 80000;

-- Find all employees born between 1970 and 1975

SELECT *

FROM employee

WHERE birth_day BETWEEN '1970-01-01' AND '1975-01-01';

-- Find all employees named Jim, Michael, Johnny or David

SELECT *

FROM employee

WHERE first_name IN ('Jim', 'Michael', 'Johnny', 'David');

FUNCTION

-- Find the number of employees

SELECT COUNT(super_id)

FROM employee;

-- Find the average of all employee's salaries

SELECT AVG(salary)

FROM employee;
-- Find the sum of all employee's salaries

SELECT SUM(salary)

FROM employee;

-- Find out how many males and females there are

SELECT COUNT(sex), sex

FROM employee

GROUP BY sex

-- Find the total sales of each salesman

SELECT SUM(total_sales), emp_id

FROM works_with

GROUP BY client_id;

-- Find the total amount of money spent by each client

SELECT SUM(total_sales), client_id

FROM works_with

GROUP BY client_id;

WILDCARDS

-- % = any # characters, _ = one character

-- Find any client's who are an LLC

SELECT *
FROM client

WHERE client_name LIKE '%LLC';

-- Find any branch suppliers who are in the label business

SELECT *

FROM branch_supplier

WHERE supplier_name LIKE '% Label%';

-- Find any employee born on the 10th day of the month

SELECT *

FROM employee

WHERE birth_day LIKE '_____10%';

-- Find any clients who are schools

SELECT *

FROM client

WHERE client_name LIKE '%Highschool%';

UNION

-- Find a list of employee and branch names

SELECT employee.first_name AS Employee_Branch_Names

FROM employee

UNION

SELECT branch.branch_name

FROM branch;
-- Find a list of all clients & branch suppliers' names

SELECT client.client_name AS Non-Employee_Entities, client.branch_id AS Branch_ID

FROM client

UNION

SELECT branch_supplier.supplier_name, branch_supplier.branch_id

FROM branch_supplier;

JOINS

-- Add the extra branch

INSERT INTO branch VALUES(4, "Buffalo", NULL, NULL);

SELECT employee.emp_id, employee.first_name, branch.branch_name

FROM employee

JOIN branch -- LEFT JOIN, RIGHT JOIN

ON employee.emp_id = branch.mgr_id;

NESTED QUERIES

-- Find names of all employees who have sold over 50,000

SELECT employee.first_name, employee.last_name

FROM employee

WHERE employee.emp_id IN (SELECT works_with.emp_id

FROM works_with

WHERE works_with.total_sales > 50000);


-- Find all clients who are handles by the branch that Michael Scott manages

-- Assume you know Michael's ID

SELECT client.client_id, client.client_name

FROM client

WHERE client.branch_id = (SELECT branch.branch_id

FROM branch

WHERE branch.mgr_id = 102);

-- Find all clients who are handles by the branch that Michael Scott manages

-- Assume you DONT'T know Michael's ID

SELECT client.client_id, client.client_name

FROM client

WHERE client.branch_id = (SELECT branch.branch_id

FROM branch

WHERE branch.mgr_id = (SELECT employee.emp_id

FROM employee

WHERE employee.first_name = 'Michael' AND employee.last_name ='Scott'

LIMIT 1));

-- Find the names of employees who work with clients handled by the scranton branch

SELECT employee.first_name, employee.last_name

FROM employee

WHERE employee.emp_id IN (

SELECT works_with.emp_id
FROM works_with

AND employee.branch_id = 2;

-- Find the names of all clients who have spent more than 100,000 dollars

SELECT client.client_name

FROM client

WHERE client.client_id IN (

SELECT client_id

FROM (

SELECT SUM(works_with.total_sales) AS totals, client_id

FROM works_with

GROUP BY client_id) AS total_client_sales

WHERE totals > 100000

);

TRIGGERS

Giraffe Academy

Courseslibrary_books

Aboutinfo_outline

Contributeattach_money

SQL

Tables & Keys

Sql Basics

Mysql Windows Installation


Mysql Mac Installation

Creating Tables

Inserting Data

Constraints

Update & Delete

Basic Queries

Company Database Intro

Creating Company Database

More Basic Queries

Functions

Wildcards

Union

Joins

Nested Queries

On Delete

Triggers

Er Diagrams Intro

Designing An Er Diagram

Er Diagram Mapping

Databases SQL Triggers

Triggers

Lesson 20

Author : 🦒

Last Updated : December, 2017


Code

Copy

-- CREATE

-- TRIGGER `event_name` BEFORE/AFTER INSERT/UPDATE/DELETE

-- ON `database`.`table`

-- FOR EACH ROW BEGIN

-- -- trigger body

-- -- this code is applied to every

-- -- inserted/updated/deleted row

-- END;

CREATE TABLE trigger_test (

message VARCHAR(100)

);

DELIMITER $$

CREATE

TRIGGER my_trigger BEFORE INSERT

ON employee

FOR EACH ROW BEGIN


INSERT INTO trigger_test VALUES('added new employee');

END$$

DELIMITER ;

INSERT INTO employee

VALUES(109, 'Oscar', 'Martinez', '1968-02-19', 'M', 69000, 106, 3);

DELIMITER $$

CREATE

TRIGGER my_trigger BEFORE INSERT

ON employee

FOR EACH ROW BEGIN

INSERT INTO trigger_test VALUES(NEW.first_name);

END$$

DELIMITER ;

INSERT INTO employee

VALUES(110, 'Kevin', 'Malone', '1978-02-19', 'M', 69000, 106, 3);

DELIMITER $$

CREATE

TRIGGER my_trigger BEFORE INSERT

ON employee

FOR EACH ROW BEGIN

IF [Link] = 'M' THEN

INSERT INTO trigger_test VALUES('added male employee');


ELSEIF [Link] = 'F' THEN

INSERT INTO trigger_test VALUES('added female');

ELSE

INSERT INTO trigger_test VALUES('added other employee');

END IF;

END$$

DELIMITER ;

INSERT INTO employee

VALUES(111, 'Pam', 'Beesly', '1988-02-19', 'F', 69000, 106, 3);

DROP TRIGGER my_trigger;

Common questions

Powered by AI

Efficient SQL data management for large datasets can be achieved through a combination of indexing, partitioning, and query optimization. Indexing allows rapid data retrieval, while partitioning can break a large table into smaller, more manageable pieces, enhancing performance. Employing views for complex queries can simplify data retrieval processes. Concurrency controls and transaction management, such as using COMMIT and ROLLBACK, ensure that only complete and correct transactions affect the database, preserving data integrity. Proper normalization and the use of stored procedures can further optimize interactions with large data .

Incorrect JOIN operations in SQL can lead to misleading results, especially in complex databases with multiple tables. Common issues include returning duplicate rows or losing data rows when inappropriate joins (INNER JOIN instead of LEFT JOIN) are used. A missing join condition can create a Cartesian product, resulting in incorrect results with an exponential number of rows. Furthermore, incorrect assumptions about keys can lead to inaccurate data aggregation and loss of referential integrity. Thus, understanding the relationships and the correct type of JOIN operation is crucial for accurate data retrieval .

Nested queries, or subqueries, in SQL are used to perform operations that need intermediate results or complex filtering criteria. They allow for operations like filtering based on aggregated results in another table, e.g., finding clients who have spent more than a certain amount by nesting subqueries to calculate SUM of sales. However, nested queries can degrade performance due to increased computational complexity, especially if not optimized or if involving large datasets. They can also be difficult to read and maintain compared to JOIN operations, which can achieve the same results more efficiently .

The AUTO_INCREMENT constraint is used in SQL to generate a unique identifier for new rows automatically. This is typically applied to a primary key column to ensure each row has a distinct value. The advantage of AUTO_INCREMENT is it simplifies the process of generating unique IDs without requiring manual input, ensuring uniqueness automatically. However, this can lead to significant gaps in numbering if rows are deleted. In high-volume tables, it might also lead to the maximum integer value being reached, requiring changes to data type or design .

The UNION operation in SQL is crucial for combining the results of two or more SELECT statements into a single result set, eliminating duplicate rows by default. It's used when needing succinct results from multiple tables that have similar structures. Practical applications include retrieving a comprehensive list of names from different entities, such as combining employee names and branch names to get a unified list of stakeholders or compiling client and supplier names to understand all external entity interactions . The key limitation is that all SELECT statements must have the same number of columns in the same order.

To alter a table structure in SQL, you can use the ALTER TABLE statement. This statement allows for various modifications: adding columns, dropping columns, changing column data types, and adding or removing constraints. For example, you can add a new column using 'ALTER TABLE student ADD gpa DECIMAL;' and drop it using 'ALTER TABLE student DROP COLUMN gpa;'. You can also change constraints on existing columns, such as setting a column to NOT NULL or UNIQUE, or defining a DEFAULT value .

Constraints in SQL are essential to ensure data integrity and validation by enforcing rules on the data in a table. They define the conditions that the data must meet for the operation to succeed, such as PRIMARY KEY, UNIQUE, NOT NULL, CHECK, and FOREIGN KEY. Constraints like PRIMARY KEY and UNIQUE ensure that no duplicate values exist in a column, while NOT NULL makes sure that a column cannot have NULL values. DEFAULT constraints provide default values for a column when no value is specified. FOREIGN KEY ensures that a value in a column must match a value in another table, thus maintaining referential integrity. These constraints are crucial for maintaining consistent and valid data .

SQL allows for the manipulation of specific rows using the UPDATE and DELETE statements. The UPDATE statement modifies one or more columns for selected rows, often using a WHERE clause to limit affected rows, e.g., 'UPDATE student SET major = 'Undecided' WHERE student_id = 4;'. The DELETE statement removes rows from a table based on conditions defined in the WHERE clause, e.g., 'DELETE FROM student WHERE student_id = 4;'. These operations enable dynamic data management and can affect database integrity and performance if not managed with proper conditions or constraints .

SQL TRIGGERS automate responses to changes in database tables by executing a specified set of operations when certain events occur, such as INSERT, UPDATE, or DELETE. They allow for automatic data validation, logging actions, or enforcing business rules. However, challenges include the potential for unexpected side effects if triggers execute too many operations, affecting performance and leading to difficult-to-trace errors. Complexity increases with nested or multiple triggers, making debugging and maintenance challenging .

Foreign keys in SQL are used to ensure referential integrity by creating a link between tables. This is managed through constraints that enforce valid relationships. For instance, a column in one table may reference a primary key in another table. The foreign key ensures that any value in this column matches a value of a primary key in the referenced table. SQL supports cascading actions on delete or update operations to maintain the integrity, such as 'ON DELETE SET NULL' or 'ON DELETE CASCADE' as seen in the employee and branch tables .

You might also like