Employee Management System - SQL Typing CST Test

Mode:

Duration:

CREATE TABLE departments (
	department_id SERIAL PRIMARY KEY,
	department_name VARCHAR(100) NOT NULL,
	location VARCHAR(100)
);

CREATE TABLE employees (
	employee_id SERIAL PRIMARY KEY,
	first_name VARCHAR(50),
	last_name VARCHAR(50),
	email VARCHAR(100) UNIQUE,
	hire_date DATE,
	salary DECIMAL(10,2),
	department_id INTEGER REFERENCES departments(department_id)
);

INSERT INTO departments (department_name, location) VALUES
	('Engineering', 'New York'),
	('Marketing', 'Chicago'),
	('Finance', 'Boston');

INSERT INTO employees (first_name, last_name, email, hire_date, salary, department_id) VALUES
	('John', 'Smith', 'john@example.com', '2022-01-15', 85000, 1),
	('Sarah', 'Johnson', 'sarah@example.com', '2021-03-10', 92000, 1),
	('Mike', 'Brown', 'mike@example.com', '2023-05-11', 70000, 2),
	('Emma', 'Davis', 'emma@example.com', '2020-07-21', 88000, 3);

SELECT
	d.department_name,
	COUNT(e.employee_id) AS total_employees,
	AVG(e.salary) AS avg_salary,
	MAX(e.salary) AS highest_salary
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;

SELECT
	first_name,
	last_name,
	salary,
	RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

CREATE VIEW employee_summary AS
SELECT
	e.employee_id,
	e.first_name,
	e.last_name,
	d.department_name,
	e.salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

SELECT * FROM employee_summary;Coding works best on desktop or with an external keyboard.

~`

!1

@2

#3

$4

%5

^6

&7

*8

(9

)0

_-

+=

⌫

Tab

Q

W

E

R

T

Y

U

I

O

P

{[

}]

|\

Caps

A

S

D

F

G

H

J

K

L

:;

"'

↵

⇧

Z

X

C

V

B

N

M

<,

>.

?/

⇧

Ctrl

⊞

Alt

␣

Alt

⊞

Ctx

Ctrl

👈Middle

L Pinky

L Ring

L Middle

L Index

Thumb

R Index

R Middle

R Ring

R Pinky

Employee Management System — SQL Code

Demonstrates employee, department, payroll, and reporting operations using joins, aggregations, views, and window functions.

CREATE TABLE departments (
	department_id SERIAL PRIMARY KEY,
	department_name VARCHAR(100) NOT NULL,
	location VARCHAR(100)
);

CREATE TABLE employees (
	employee_id SERIAL PRIMARY KEY,
	first_name VARCHAR(50),
	last_name VARCHAR(50),
	email VARCHAR(100) UNIQUE,
	hire_date DATE,
	salary DECIMAL(10,2),
	department_id INTEGER REFERENCES departments(department_id)
);

INSERT INTO departments (department_name, location) VALUES
	('Engineering', 'New York'),
	('Marketing', 'Chicago'),
	('Finance', 'Boston');

INSERT INTO employees (first_name, last_name, email, hire_date, salary, department_id) VALUES
	('John', 'Smith', 'john@example.com', '2022-01-15', 85000, 1),
	('Sarah', 'Johnson', 'sarah@example.com', '2021-03-10', 92000, 1),
	('Mike', 'Brown', 'mike@example.com', '2023-05-11', 70000, 2),
	('Emma', 'Davis', 'emma@example.com', '2020-07-21', 88000, 3);

SELECT
	d.department_name,
	COUNT(e.employee_id) AS total_employees,
	AVG(e.salary) AS avg_salary,
	MAX(e.salary) AS highest_salary
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;

SELECT
	first_name,
	last_name,
	salary,
	RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

CREATE VIEW employee_summary AS
SELECT
	e.employee_id,
	e.first_name,
	e.last_name,
	d.department_name,
	e.salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

SELECT * FROM employee_summary;

SQL Language Guide

SQL (Structured Query Language) is a standard language for managing and manipulating relational databases, enabling querying, insertion, updating, and deletion of data efficiently.

Primary Use Cases

▸Querying relational data for applications
▸Data aggregation and reporting
▸Transaction management in business systems
▸Analytics and business intelligence
▸Database schema definition and data integrity enforcement

Notable Features

▸Declarative syntax for querying data
▸Support for CRUD operations: Create, Read, Update, Delete
▸Joins and subqueries for combining data
▸Transactions to ensure atomicity and consistency
▸Data definition language (DDL) for schema management

Origin & Creator

Developed at IBM in the early 1970s by Donald D. Chamberlin and Raymond F. Boyce.

Industrial Note

SQL is ubiquitous in enterprise, analytics, web applications, and data-intensive domains where structured relational data is used.

Quick Explain

▸SQL provides declarative commands to interact with relational database systems (RDBMS).
▸Supports querying, filtering, aggregation, joins, and transactions.
▸Used across major RDBMS like MySQL, PostgreSQL, Oracle, SQL Server, and SQLite.
▸Enforces schema, data types, constraints, and relationships.
▸Enables both simple queries and complex analytical operations.

Core Features

▸SELECT queries with filtering and ordering
▸INSERT, UPDATE, DELETE operations
▸JOINs (INNER, LEFT, RIGHT, FULL) and subqueries
▸GROUP BY, HAVING, and aggregate functions
▸Transactions and constraint enforcement

Learning Path

▸Understand relational data modeling
▸Learn basic CRUD operations
▸Master joins, subqueries, and aggregation
▸Study indexing, constraints, and transactions
▸Practice database optimization and analytics queries

Practical Examples

▸SELECT data with WHERE filters
▸JOIN multiple tables for relational queries
▸Aggregate data using SUM, COUNT, AVG, MAX, MIN
▸Create and manage tables with constraints
▸Use transactions to ensure atomic operations

Comparisons

▸SQL vs NoSQL: SQL for structured data, NoSQL for flexible schema/unstructured data
▸MySQL vs PostgreSQL: MySQL widely used, PostgreSQL more advanced features
▸SQL vs GraphQL: SQL queries relational data, GraphQL queries API endpoints
▸SQL vs ORM query languages: SQL native and powerful, ORM abstracts complexity
▸SQL vs Excel: SQL handles large datasets efficiently, Excel for small-scale analysis

Strengths

▸Standardized and widely supported across RDBMS
▸Powerful for structured data manipulation
▸Enables complex queries and analytics
▸ACID-compliant transactions ensure data reliability
▸Strong community and documentation support

Limitations

▸Less flexible for unstructured or hierarchical data
▸Complex queries can be hard to optimize
▸Performance depends on indexing and schema design
▸Portability issues with vendor-specific SQL extensions
▸Limited in handling very large-scale distributed data compared to NoSQL

When NOT to Use

▸For unstructured or schema-less data (use NoSQL)
▸When horizontal scaling of huge datasets is primary concern
▸Rapid prototyping of small, transient datasets
▸Applications requiring real-time streaming analytics only
▸When fully distributed databases are preferred

Cheat Sheet

▸SELECT * FROM table WHERE condition - basic query
▸INSERT INTO table (columns) VALUES (values) - add data
▸UPDATE table SET column=value WHERE condition - modify data
▸DELETE FROM table WHERE condition - remove data
▸CREATE TABLE table_name (columns) - define schema

FAQ

▸Is SQL open-standard? -> Yes, ANSI/ISO standard.
▸Does SQL work on all RDBMS? -> Core syntax is standard, but extensions vary.
▸Can SQL handle large datasets? -> Yes, with indexing and optimization.
▸Is SQL suitable for unstructured data? -> No, consider NoSQL for that.
▸How do you prevent SQL injection? -> Use parameterized queries and prepared statements.

30-Day Skill Plan

▸Week 1: Basic SELECT, INSERT, UPDATE, DELETE
▸Week 2: Joins, GROUP BY, and aggregate functions
▸Week 3: Subqueries, CTEs, and window functions
▸Week 4: Transactions, constraints, and indexing
▸Week 5: Performance tuning and backup strategies

Final Summary

▸SQL is the standard language for relational database management.
▸Enables querying, updating, and managing structured data.
▸Supports transactions, constraints, and complex joins.
▸Widely adopted in enterprise, analytics, and web applications.
▸Works with most RDBMS, with strong community and tooling support.

Project Structure

▸Database instance
▸Schemas for logical separation
▸Tables representing entities
▸Views for reusable queries
▸Stored procedures/functions for business logic

Monetization

▸Many RDBMS are open-source (MySQL, PostgreSQL, SQLite)
▸Enterprise editions (Oracle, SQL Server) provide advanced features
▸Core SQL skills valuable in IT, data, and analytics jobs
▸Optimized SQL improves application performance
▸Foundation for business intelligence and reporting

Productivity Tips

▸Use parameterized queries for security
▸Index frequently queried columns
▸Normalize data to reduce redundancy
▸Use views and stored procedures for reusable logic
▸Profile queries regularly for performance

Basic Concepts

▸Database - container for related tables
▸Table - collection of rows with defined columns
▸Row/Record - single entry in a table
▸Column/Field - attribute of a table with data type
▸Primary/Foreign Keys - enforce uniqueness and relationships

Official Docs

▸https://www.iso.org/standard/63555.html (SQL standard)
▸MySQL Documentation
▸PostgreSQL Documentation
▸Oracle SQL Reference
▸SQL Server Docs

More SQL Typing Exercises

PostgreSQL Advanced Queries SQL Inventory Management SQL Customer Segmentation SQL Customer Segmentation SQL Financial Transactions Analysis SQL Banking System Queries SQL Order Processing Workflow SQL Order Processing Workflow SQL Order Processing Workflow SQL Healthcare Records Management SQL University Management System SQL Library Management Database

Practice Other Languages

C React Python C++Rust TypeScript Kotlin PHP Java C#Ruby Mql Cql N1ql Cypher

CREATE TABLE departments (
department_id SERIAL PRIMARY KEY,
department_name VARCHAR(100) NOT NULL,
location VARCHAR(100)
);

CREATE TABLE employees (
employee_id SERIAL PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100) UNIQUE,
hire_date DATE,
salary DECIMAL(10,2),
department_id INTEGER REFERENCES departments(department_id)
);

INSERT INTO departments (department_name, location) VALUES
('Engineering', 'New York'),
('Marketing', 'Chicago'),
('Finance', 'Boston');

INSERT INTO employees (first_name, last_name, email, hire_date, salary, department_id) VALUES
('John', 'Smith', 'john@example.com', '2022-01-15', 85000, 1),
('Sarah', 'Johnson', 'sarah@example.com', '2021-03-10', 92000, 1),
('Mike', 'Brown', 'mike@example.com', '2023-05-11', 70000, 2),
('Emma', 'Davis', 'emma@example.com', '2020-07-21', 88000, 3);

SELECT
d.department_name,
COUNT(e.employee_id) AS total_employees,
AVG(e.salary) AS avg_salary,
MAX(e.salary) AS highest_salary
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;

SELECT
first_name,
last_name,
salary,
RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

CREATE VIEW employee_summary AS
SELECT
e.employee_id,
e.first_name,
e.last_name,
d.department_name,
e.salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

SELECT * FROM employee_summary;

Coding works best on desktop or with an external keyboard.

Employee Management System — SQL Code

Demonstrates employee, department, payroll, and reporting operations using joins, aggregations, views, and window functions.

CREATE TABLE departments ( department_id SERIAL PRIMARY KEY, department_name VARCHAR(100) NOT NULL, location VARCHAR(100) ); CREATE TABLE employees ( employee_id SERIAL PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), email VARCHAR(100) UNIQUE, hire_date DATE, salary DECIMAL(10,2), department_id INTEGER REFERENCES departments(department_id) ); INSERT INTO departments (department_name, location) VALUES ('Engineering', 'New York'), ('Marketing', 'Chicago'), ('Finance', 'Boston'); INSERT INTO employees (first_name, last_name, email, hire_date, salary, department_id) VALUES ('John', 'Smith', 'john@example.com', '2022-01-15', 85000, 1), ('Sarah', 'Johnson', 'sarah@example.com', '2021-03-10', 92000, 1), ('Mike', 'Brown', 'mike@example.com', '2023-05-11', 70000, 2), ('Emma', 'Davis', 'emma@example.com', '2020-07-21', 88000, 3); SELECT d.department_name, COUNT(e.employee_id) AS total_employees, AVG(e.salary) AS avg_salary, MAX(e.salary) AS highest_salary FROM departments d LEFT JOIN employees e ON d.department_id = e.department_id GROUP BY d.department_name; SELECT first_name, last_name, salary, RANK() OVER (ORDER BY salary DESC) AS salary_rank FROM employees; CREATE VIEW employee_summary AS SELECT e.employee_id, e.first_name, e.last_name, d.department_name, e.salary FROM employees e JOIN departments d ON e.department_id = d.department_id; SELECT * FROM employee_summary;

SQL Language Guide

SQL (Structured Query Language) is a standard language for managing and manipulating relational databases, enabling querying, insertion, updating, and deletion of data efficiently.

Primary Use Cases

▸Querying relational data for applications
▸Data aggregation and reporting
▸Transaction management in business systems
▸Analytics and business intelligence
▸Database schema definition and data integrity enforcement

Notable Features

▸Declarative syntax for querying data
▸Support for CRUD operations: Create, Read, Update, Delete
▸Joins and subqueries for combining data
▸Transactions to ensure atomicity and consistency
▸Data definition language (DDL) for schema management

Origin & Creator

Developed at IBM in the early 1970s by Donald D. Chamberlin and Raymond F. Boyce.

Industrial Note

SQL is ubiquitous in enterprise, analytics, web applications, and data-intensive domains where structured relational data is used.