Dr.
B R Ambedkar National Institute of Technology
Jalandhar
IEA-544 Management Information System
Assignment 03
10 Basic SQL Commands
Submitted by:
Udit Narain Gupta (24205309)
Nelson Kujur (24205303)
Submitted To: Prof. Bikash Kumar
M.Tech. Industrial Engineering and Data Analytics
Industrial and Production Engineering Department
1.CREATE TABLE in SQL
The CREATE TABLE statement in SQL is used to define a new table in a database. It specifies
the table name, the columns, and their respective data types. Tables are fundamental to relational
databases, as they store structured data in rows and columns.
Constraints in CREATE TABLE
1. PRIMARY KEY:
a. Ensures that each value in the column is unique and not null.
2. NOT NULL:
a. Prevents null values in a column.
3. UNIQUE:
a. Ensures all values in a column are distinct.
4. CHECK:
a. Enforces a condition on the values (e.g., CHECK (salary > 0)).
5. FOREIGN KEY:
a. Links a column to another table to maintain relationships.
Syntax
sql
CREATE TABLE table_name (
column_name1 data_type constraints,
column_name2 data_type constraints,
...
column_nameN data_type constraints
);
Example: Creating a Table
Example with Relationships
Scenario: Creating tasks and linking projects to it.
SQL CODE:
CREATE TABLE projects (
project_id INT PRIMARY KEY,
project_name VARCHAR(100) NOT NULL,
start_date DATE,
end_date DATE
);
CREATE TABLE tasks (
task_id INT PRIMARY KEY,
task_name VARCHAR(100) NOT NULL,
status VARCHAR(20) DEFAULT 'Pending',
assigned_to VARCHAR(50),
project_id INT,
FOREIGN KEY (project_id) REFERENCES projects(project_id)
);
2. INSERT Command in MySQL
The INSERT statement in MySQL is used to add new rows (records) into a table in a database. It
allows data entry into specific or all columns of the table.
Syntax of INSERT
Basic Syntax
sql
Copy code
INSERT INTO table_name (column1, column2, ..., columnN)
VALUES (value1, value2, ..., valueN);
Example: Inserting in the Table created previously using CREATE in
previous example
INSERT INTO projects (project_id, project_name, start_date, end_date) VALUES
(1, 'Website Development', '2024-01-01', '2024-06-30'),
(2, 'Marketing Campaign', '2024-02-01', '2024-05-31'),
(3, 'Product Launch', '2024-03-01', '2024-07-31');
INSERT INTO tasks (task_id, task_name, status, assigned_to, project_id) VALUES
(101, 'Design UI/UX', 'Completed', 'Alice', 1),
(102, 'Develop Backend', 'In Progress', 'Bob', 1),
(103, 'Social Media Ads', 'Pending', 'Charlie', 2),
(104, 'Launch Strategy', 'Pending', 'Diana', 3);
Output:
3. DESCRIBE Command in MySQL
The DESCRIBE statement in MySQL is used to view the structure of a table, including its
columns, data types, nullability, key information, default values, and other details. This
command is particularly useful for understanding the schema of an existing table.
Syntax
DESCRIBE table_name;
Example
Table Tasks:
SQL Command
sql
DESCRIBE tasks;
Output
4. RENAME Command
The RENAME TABLE statement in MySQL is used to change the name of a table. This
command is particularly useful when restructuring databases or changing table names to align
with updated naming conventions or project requirements.
Syntax
Sql
RENAME TABLE old_table_name TO new_table_name;
Example
Command
sql
RENAME TABLE tasks TO project_tasks;
Output
5.ORDER BY Command in MySQL
The ORDER BY statement in MySQL is used to sort the result set of a query based on one or
more columns in ascending (default) or descending order. This command is essential for
organizing and presenting data in a meaningful sequence.
Syntax
sql
SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC];
ASC: Sorts in ascending order (default behavior).
DESC: Sorts in descending order.
Example
Before: Table employees
Command
Sql
SELECT *
FROM project_tasks
ORDER BY status;
After: Table employees
6. WHERE Command in MySQL
The WHERE command in MySQL is used to filter records and retrieve only those that satisfy a
given condition. It can be applied with various SQL commands like SELECT, UPDATE, and
DELETE to restrict the operation to specific rows.
Syntax
Sql
SELECT column1, column2, ...
FROM table_name
WHERE condition;
● condition can involve comparisons (e.g., =, >, <, !=), logical operators (AND, OR), or
patterns (e.g., LIKE).
Usage Example : SELECT with WHERE Used to filter records and retrieve only
those that meet the condition.
Example:
sql
SELECT *
FROM project_tasks
WHERE assigned_to = 'Charlie';
Table: employees
7.UPDATE command in MySQL
The UPDATE command in MySQL is used to modify existing records in a table. You can
update one or more columns in specific rows based on a condition defined in the WHERE
clause.
Syntax
Sql
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
● table_name: The name of the table to update.
● SET: Specifies the column(s) and their new value(s).
● WHERE: Filters the rows to be updated based on the condition. Without a WHERE
clause, all rows will be updated.
Example: Used to modify specific records that meet the condition.
Example:
sql
UPDATE project_tasks
SET status = 'Completed'
WHERE project_id = 2;
Table before UPDATE:
Table: employees
Table after UPDATE:
8. GROUP BY in SQL
The GROUP BY command in SQL is used to group rows that have the same values in specified
columns into summary rows, like "grouping by department" or "grouping by customer." It is
commonly used with aggregate functions (like COUNT, SUM, AVG, MAX, MIN) to perform
calculations on each group of rows.
How GROUP BY Works
1. Groups the rows based on the values in one or more columns.
2. Performs aggregate operations (e.g., summing values, counting rows) on each group.
Syntax
SELECT column_name, aggregate_function(column_name)
FROM table_name
GROUP BY column_name;
Example : Counting TASKS
Query: Count TASKS
Sql
SELECT status, COUNT(task_id) AS total_tasks
FROM project_tasks
GROUP BY status;
Output:
9. HAVING Command in MySQL
The HAVING command in MySQL is used to filter the results of a query based on aggregate
functions (like COUNT, SUM, AVG, etc.) after the GROUP BY operation. While WHERE is
used to filter rows before grouping, HAVING is used to filter groups after aggregation.
Syntax
sql
SELECT column1, column2, ..., aggregate_function(column)
FROM table_name
WHERE condition
GROUP BY column
HAVING aggregate_condition;
● aggregate_function(column): An aggregate function such as COUNT(), SUM(), AVG(),
etc.
● HAVING aggregate_condition: A condition on the result of the aggregate function.
Usage Example
1. Using HAVING with GROUP BY
Let's consider a table PROJECT_TASKS with the following data:
Table: Project Tasks
Using HAVING to Filter Groups
Command:
sql
SELECT status, COUNT(task_id) AS total_tasks
FROM project_tasks
GROUP BY status
HAVING COUNT(task_id) > 1;
10. LIMIT Command in MySQL
The LIMIT command in MySQL is used to restrict the number of rows returned by a query. It is
especially useful for pagination or when you only need a subset of data.
Syntax
sql
SELECT column1, column2, ...
FROM table_name
LIMIT number_of_rows;
Usage Examples
1. Limiting the Number of Rows Returned
Example:
sql
SELECT *
FROM project_tasks
LIMIT 2;
Explanation:
● This query will return the first 2 rows from the project_tasks table.
Table: employees
Output:
