Application of SQL Commands for Structure Creation and Alteration

 

Application of SQL Commands for Structure Creation and Alteration

Structured Query Language (SQL) is a powerful language used to interact with and manage relational databases. Among the many operations SQL supports, creating and altering database structures are fundamental for defining how data is stored and organized. These operations typically involve creating tables, modifying existing tables, and defining or altering constraints.

1. Structure Creation

Structure creation in SQL usually involves creating a new database, table, or other database objects (like indexes, views, or schemas). The most common use case is creating tables that define the structure of data.

Creating a Table

The CREATE TABLE statement is used to define a new table along with its columns and data types.

Syntax:

CREATE TABLE table_name ( column1 datatype [constraints], column2 datatype [constraints], ... columnN datatype [constraints] );

EXAMPLE

CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50), BirthDate DATE, HireDate DATE, Salary DECIMAL(10, 2)

b. Creating Tables

Tables are the fundamental structures for storing data in a database.

CREATE TABLE table_name ( column1 datatype constraints, column2 datatype constraints, ...);

• column1, column2, ...: These represent the names of the columns.
• datatype: This defines the type of data the column will hold (e.g., INT, VARCHAR, DATE).
• constraints: Optional rules like PRIMARY KEY, NOT NULL, UNIQUE, etc.

Example:

CREATE TABLE Employees (EmployeeID INT PRIMARY KEY, FirstName VARCHAR(50) NOT NULL, LastName VARCHAR(50) NOT NULL, HireDate DATE);

This command creates an "Employees" table with columns for employee ID, first name, last name, and hire date.

2. Altering Structures in DBMS

a. Adding a Column

To add a new column to an existing table:

ALTER TABLE table_name ADD column_name datatype constraints;

Example:

ALTER TABLE EmployeesnADD Email VARCHAR(100);

This command adds an Email column to the Employees table.

b. Modifying a Column

To change the datatype or constraints of an existing column:

ALTER TABLE table_name MODIFY column_name new_datatype constraints;

Example:

ALTER TABLE Employees MODIFY Email VARCHAR(150) NOT NULL;

This command modifies the Email column to have a length of 150 characters and not allow NULL values.

 

c. Dropping a Column

To remove a column from a table:

ALTER TABLE table_name DROP COLUMN column_name;

Example:

ALTER TABLE Employees DROP COLUMN HireDate;

This command removes the HireDate column from the Employees table.

 

3. Dropping Structures in DBMS

a. Dropping Tables

To completely remove a table from the database:

DROP TABLE table_name;

Example:

DROP TABLE Employees;

This command deletes the Employees table and all the data stored in it.

b. Dropping a Database

To remove an entire database:

DROP DATABASE database_name;

Example:

DROP DATABASE company;

This command deletes the company database and all the tables within it.

 

4. Renaming Structures

a. Renaming a Table

To rename an existing table:

ALTER TABLE old_table_name

RENAME TO new_table_name;

Example:

ALTER TABLE Employees RENAME TO Staff;

This command renames the Employees table to Staff.

 

SQL provides robust commands for creating, altering, and dropping database structures, enabling efficient database management and adaptability to changing requirements. These commands are essential tools for database administrators and developers, ensuring data integrity and organization

 

Defining Constraints in SQL

Constraints are rules applied to columns in a database table to ensure the accuracy and reliability of the data. They define how the data in a table can be inserted, updated, and deleted. Here's an overview of some commonly used SQL constraints:

1. PRIMARY KEY

Definition:
The PRIMARY KEY constraint uniquely identifies each record in a table. A table can have only one primary key, which may consist of a single column or multiple columns (composite key). The values in the primary key column(s) must be unique and cannot be NULL.

A primary key is a field which can uniquely identify each row in a table. This constraint is used to specify a field in a table as the primary key.

Characteristics:

• Ensures that the column(s) have unique values.
• Implicitly defines the column(s) as NOT NULL.

 

Syntax:

CREATE TABLE table_name ( column_name datatype PRIMARY KEY,  ...);

Example:

CREATE TABLE Employees (EmployeeID INT PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50),HireDate DATE);

Explanation:

• EmployeeID is defined as the primary key, ensuring that each employee has a unique identifier.

2. FOREIGN KEY

Definition:
The FOREIGN KEY constraint links two tables together by establishing a relationship between a column in one table and the primary key or unique key in another table. It ensures that the values in the foreign key column correspond to values in the referenced column, maintaining referential integrity.

Syntax:

CREATE TABLE table_name (column_name datatype, FOREIGN KEY (column_name) REFERENCES parent_table_name(parent_column_name));

Example:

CREATE TABLE Orders (OrderID INT PRIMARY KEY,  OrderDate DATE, EmployeeID INT,

    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID));

• EmployeeID in the Orders table is a foreign key that references EmployeeID in the Employees table. This ensures that each order is linked to an existing employee.

3. UNIQUE

Definition:
The UNIQUE constraint ensures that all values in a column (or a group of columns) are distinct. Unlike the primary key, a table can have multiple unique constraints, and the column(s) can contain NULL values (though only one NULL value per column is typically allowed).

Syntax:

CREATE TABLE table_name (column_name datatype UNIQUE, ...);

Example:

CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, Email VARCHAR(100) UNIQUE,

    FirstName VARCHAR(50), LastName VARCHAR(50));

Description:

• The Email column is unique, ensuring that no two employees can have the same email address.

 

4. NOT NULL

Definition:
The NOT NULL constraint ensures that a column cannot have a NULL value. This constraint is used when a column must always contain a value.

Syntax:

CREATE TABLE table_name (  column_name datatype NOT NULL, ...);

Example:

SQL

CREATE TABLE Employees (EmployeeID INT PRIMARY KEY, FirstName VARCHAR(50) NOT NULL, LastName VARCHAR(50) NOT NULL);

Description:

• Both FirstName and LastName are defined as NOT NULL, meaning that each employee must have a first and last name.

 

5. CHECK

Definition:
The CHECK constraint limits the values that can be placed in a column by enforcing a condition. The condition must be true for the data to be inserted or updated.

Syntax:

CREATE TABLE table_name (column_name datatype CHECK (condition), ...);

Example:

SQL

CREATE TABLE Employees (EmployeeID INT PRIMARY KEY FirstName VARCHAR(50) NOT NULL, LastName VARCHAR(50) NOT NULL, Salary DECIMAL(10, 2),CHECK (Salary > 0));

Description:

• The CHECK constraint ensures that the Salary column can only contain positive values.

 

6. IN Operator

Definition:
The IN operator is not a constraint but a conditional operator used in SQL WHERE clauses. It allows you to specify multiple possible values for a column. It checks if the value of a column is within a specified set of values.

Syntax:

SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, ...);

Example:

SELECT * FROM Employees WHERE DepartmentID IN (1, 2, 3);

Explanation:

• This query selects all employees who belong to departments with DepartmentID values of 1, 2, or 3.

7. DEFAULT

The DEFAULT constraint in SQL is used to provide a default value for a column when no value is specified during an insert operation. This ensures that a column always has a valid value, even if one is not provided explicitly.

Characteristics:

• Automatically assigns a specified default value to a column if no value is supplied during insertion.
• Helps prevent NULL values in a column where they might be undesirable.
• Can be applied to any data type.

Syntax:

CREATE TABLE table_name (column_name datatype DEFAULT default_value, ...);

Example:

creating a table called Orders where each order should have a default status of 'Pending' if no status is specified.

CREATE TABLE Orders (OrderID INT PRIMARY KEY, OrderDate DATE, OrderStatus VARCHAR(20) DEFAULT 'Pending');

In this example:

• If a new order is inserted without specifying an OrderStatus, it will automatically be set to 'Pending'.

Inserting Data with and without the DEFAULT Value:

-- Inserting data without specifying OrderStatus

INSERT INTO Orders (OrderID, OrderDate)

VALUES (1, '2024-08-17');

 

-- Inserting data with a specified OrderStatus

INSERT INTO Orders (OrderID, OrderDate, OrderStatus)

VALUES (2, '2024-08-18', 'Shipped');

• The first insert statement will insert a row where OrderStatus is 'Pending' by default.
• The second insert statement will insert a row with OrderStatus as 'Shipped'.

 

Using DEFAULT with ALTER TABLE:

add a default value to an existing column using the ALTER TABLE command:

 

ALTER TABLE Orders

ADD CONSTRAINT df_OrderStatus DEFAULT 'Pending' FOR OrderStatus;

 

The DEFAULT constraint simplifies data entry by automatically providing a specified value for columns where no value is explicitly given. This is especially useful for maintaining consistent data states and avoiding NULL values in scenarios where a column should always have a meaningful default value.

Constraints are essential tools in SQL for maintaining the integrity and accuracy of data in a database. They enforce rules that ensure data consistency and validity, making them a critical part of database design. Understanding and properly applying constraints like PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, and using operators like IN are fundamental skills for anyone working with relational databases.

 

Aggregation

Aggregation is a concept wherein the connection between two entities is considered as one unified entity. It includes combining the relationships with their respective entities into a better-degree entity.

When it comes to statistical information in a DBMS, there are extraordinary gears and approaches to collect it, too:

• SUM: It is used for adding things up.

• AVG: It is used for finding the middle ground.

• Minimum (MIN): It is used to get the singling out of the smallest values.

• Maximum (MAX): It is used for singling out for the largest values.

• COUNT: It is used for a headcount and DISTINCT for counting unique items.

• Filtering Aggregated Data: Picture sifting through a treasure trove to find the real gems: We use the HAVING clause to filter out the noise and focus on the valuable insights.

Examples of Aggregation

Here is a scenario that represents the diagrammatic representations of the Aggregation in the Database Management System.

Applications of Aggregation in DBMS

1. Information era

• Aggregation makes it simpler to test record a while within the DBMS.

• Create and summarize records to create precise services tailor-made to the precise needs of the corporation.

2. Strength Assessment Questions

• Collection is vital for successful interviews.

• Helps extract treasured insights from facts sets of equal size by means of successfully solving complex queries.

3. Enhancing Data Retrieval Efficiency

• Archiving improves records retrieval overall performance in a DBMS.

• Precomputation and storage of combination values ​​reduces the computation time of query operations, resulting in quicker recovery instances. 

Aggregate functions in SQL are used to perform calculations on a set of values, returning a single value as a result. These functions are commonly used in conjunction with the GROUP BY clause to summarize data. Here’s an overview of the most commonly used aggregate functions:

Here are some of the most commonly used SQL aggregate functions:

• COUNT(): Counts the number of rows in a dataset.
• SUM(): Calculates the total sum of a numeric column.
• AVG(): Calculates the average value of a numeric column.
• MIN(): Finds the minimum value in a column.
• MAX(): Finds the maximum value in a column.

 

1. COUNT()

The COUNT() function returns the number of rows that match a specified condition. It can be used with all data types.

Syntax:

SELECT COUNT(column_name)

FROM table_name

WHERE condition;

Example:

SELECT COUNT(EmployeeID) FROM Employees WHERE Department = 'IT';

This query returns the number of employees in the "IT" department.

2. SUM()

The SUM() function returns the total sum of a numeric column.

Syntax:

SELECT SUM(column_name) FROM table_name WHERE condition;

Example:

SELECT SUM(Salary) FROM Employees WHERE Department = 'Finance';

This query calculates the total salary of all employees in the "Finance" department.

3. AVG()

The AVG() function calculates the average value of a numeric column.

Syntax:

SELECT AVG(column_name) FROM table_name WHERE condition;

Example:

SELECT AVG(Salary) FROM Employees WHERE Department = 'HR';

This query returns the average salary of employees in the "HR" department.

 

4. MIN()

The MIN() function returns the smallest value in a specified column.

Syntax:

SELECT MIN(column_name) FROM table_name WHERE condition;

Example:

SELECT MIN(Salary) FROM Employees;

This query returns the lowest salary in the Employees table.

5. MAX()

The MAX() function returns the largest value in a specified column.

Syntax:

SELECT MAX(column_name) FROM table_name WHERE condition;

Example:

SELECT MAX(Salary) FROM Employees;

This query returns the highest salary in the Employees table.

6. GROUP BY Clause

The GROUP BY clause is often used with aggregate functions to group the result set by one or more columns. It allows you to perform aggregate calculations on each group separately.

Syntax:

SELECT column_name, AGGREGATE_FUNCTION(column_name) FROM table_name WHERE condition

GROUP BY column_name;

Example:

SELECT Department, COUNT(EmployeeID) FROM Employees GROUP BY Department;

This query returns the number of employees in each department.

7. HAVING Clause

The HAVING clause is used to filter groups based on the result of aggregate functions, similar to how the WHERE clause filters rows.

Syntax:

SELECT column_name, AGGREGATE_FUNCTION(column_name)FROM table_name

GROUP BY column_name HAVING AGGREGATE_FUNCTION(column_name) condition;

Example:

SELECT Department, COUNT(EmployeeID) FROM Employees GROUP BY Department

HAVING COUNT(EmployeeID) > 5;

This query returns departments with more than 5 employees.

 

Aggregate functions like COUNT(), SUM(), AVG(), MIN(), and MAX() are powerful tools for summarizing and analysing data in SQL. Combined with the GROUP BY and HAVING clauses, they allow for sophisticated data queries, making it easier to gain insights and generate reports based on the data stored in a database.

 

Built-in Functions

 

SQL functions are powerful tools that help in performing complex calculations and data manipulation with ease. The functions in SQL can edit rows and tables, alter strings, and help you manage organized and easy-to-navigate databases.

A function is a special type of predefined command set that performs some operation and returns a value. Functions operate on zero, one, two, or more values that are provided to them. The values that are provided to functions are called parameters or arguments.

The SQL functions have been categorized into various categories, such as String functions, Mathematical functions, Date and Time functions, etc.

1.      Numeric Functions

These functions perform operations on numeric data.

1. ABS(): Returns the absolute value of a number.

             SELECT ABS(-10);  -- Result: 10

2. CEILING(): Rounds a number up to the nearest integer.

             SELECT CEILING(4.2);  -- Result: 5

3. FLOOR(): Rounds a number down to the nearest integer.

             SELECT FLOOR(4.7);  -- Result: 4

4. ROUND(): Rounds a number to a specified number of decimal places.

             SELECT ROUND(123.456, 2);  -- Result: 123.46

5. POWER(): Returns the value of a number raised to the power of another number.

             SELECT POWER(2, 3);  -- Result: 8

6. SQRT(): Returns the square root of a number.

             SELECT SQRT(16);  -- Result: 4

The numeric functions in MySQL accept numeric values, perform a mathematic operation on the values and return resulting sheet. Some useful numeric functions are:

S.No.	Function	Description	Example
1.	MOD()	Returns the remainder of one expression by diving y another expression.	SELECT MOD(11, 4) “Modulus” ;
2.	POWER()/POW()	Returns the value of one expression raised to the power of another expression	SELECT POWER(3, 2) “Raised” ;
3.	ROUND()	Returns numeric expression rounded to an integer. Can be used to round an expression to a number of decimal points.	SELECT ROUND(15.193, 1) “Round” ;
4.	SIGN()	This function returns sign of a given number.	SELECT SIGN(-15) “Sign” ;
5.	SQRT()	Returns the non-negative square root of numeric expression.	SELECT SQRT(26) “Square root” ;
6.	TRUNCATE()	Returns numeric exp1 truncate to exp2 decimal places. If exp2 is 0, then the result will have no decimal point	DRLRCT TRUNCATE(15.79, 1) “Truncate” ;

 

2.      Date Functions

These functions operate on date and time values.

1. GETDATE(): Returns the current date and time.

             SELECT GETDATE();  -- Result: 2024-08-17 14:30:00.000 (example)

2. DATEADD(): Adds a specified time interval to a date.

             SELECT DATEADD(DAY, 5, '2024-08-17');  -- Result: 2024-08-22

3. DATEDIFF(): Returns the difference between two dates.

             SELECT DATEDIFF(DAY, '2024-08-01', '2024-08-17');  -- Result: 16

4. DATEPART(): Returns a specified part of a date (e.g., year, month, day).

             SELECT DATEPART(YEAR, '2024-08-17');  -- Result: 2024

5. FORMAT(): Returns a date formatted according to a specified format.

             SELECT FORMAT(GETDATE(), 'yyyy-MM-dd');  -- Result: 2024-08-17

Date and Time functions in SQL are used to manipulate and transform date and time data stored in tables. Date functions operate on values of the DATE datatype.

Some useful date and time functions are:

S.No.	Function	Description	Example
1	CURDATE()/ CURRENT_DATE()/ CURRENT_DATE	Returns the current date.	SELECT CURDATE() ;
2	DATE()	Extracts the date part of a date or date-time expression.	SELECT DATE(‘2020-12-31 01:02:03’) ;
3	MONTH()	Returns the month from the date passed.	SELECT MONTH(‘2020-12-31’) ;
4	YEAR()	Returns the year	SELECT YEAR(‘2020-12-31’) ;
5	NOW()	Returns the time at which the function executes.	SELECT NOW() ;
6	SYSDATE()	Returns the current date and time.	SELECT NOW(), SLEEP(2), NOW() ; or SELECT SYSDATE(), SLEEP(2), SYSDATE() ;

 

3.                String Functions

These functions manipulate string data.

1. LEN(): Returns the length of a string.

             SELECT LEN('Hello World');  -- Result: 11

2. UPPER(): Converts a string to uppercase.

             SELECT UPPER('hello');  -- Result: HELLO

3. LOWER(): Converts a string to lowercase.

            SELECT LOWER('HELLO');  -- Result: hello

4. SUBSTRING(): Extracts a substring from a string.

             SELECT SUBSTRING('Hello World', 1, 5);  -- Result: Hello

5. REPLACE(): Replaces occurrences of a specified substring with another substring.

             SELECT REPLACE('Hello World', 'World', 'SQL');  -- Result: Hello SQL

6. CONCAT(): Concatenates two or more strings.

             SELECT CONCAT('Hello', ' ', 'World');  -- Result: Hello World

The string functions of MySQL are used to manipulate and transform the text string. Some commonly used string functions are:

S.No.	Function	Description	Examples
1.	definitions	Returns the character for each integer passes	1. SELECT CHAR(70, 65, 67, 69) ; 2. SELECT CHAR(65, 67.3, 69.3) ;
2.	CONCAT()	Returns concatenated string	SELECT CONCAT(name, aggregate) AS “Name Marks” FROM student WHERE age = 14 OR age = 16;
3.	LOWER() /LCASE()	Returns the argument in lowercase	SELECT LOWER(‘GEEKSFORGEEKS’) AS “LowerName1”, LOWER(‘Geeks For Geeks’) AS “LowerName2” ;
4.	SUBSTRING(), SUBSTR()	Returns the substring as specified	1. SELECT SUBSTR(‘ABSDEFG’, 3, 4) “Subs” ; 2. SELECT SUBSTR(‘ABCDEFG’, -5, 4) “Subs” ;
5.	UPPER()/UCASE()	Converts to uppercase	SELECT UPPER(‘Large’) “Uppercase” ; or SELECT UCASE(‘Large’) “Uppercase”;
6.	TRIM()	Removes leading and trailing spaces	SELECT TRIM(‘Bar One’) ;
7.	LENGTH()	Returns the length of a string in bytes	SELECT LENGTH(‘CANDIDE’) “Length in characters” ;