Scribd
Upload
11 views1 page

SQL Commands for Employee Management

Chapter 2 of the SQL in Action Worksheet covers fundamental SQL commands such as CREATE TABLE, INSERT, UPDATE, and DELETE, along with their specific purposes and differences. It includes practical tasks for creating and manipulating an employee table, demonstrating various SQL operations like adding columns, querying data, and modifying table structures. The chapter emphasizes the importance of clauses like WHERE and commands like ALTER TABLE in managing database tables.

Uploaded by

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

SQL Commands for Employee Management

Chapter 2 of the SQL in Action Worksheet covers fundamental SQL commands such as CREATE TABLE, INSERT, UPDATE, and DELETE, along with their specific purposes and differences. It includes practical tasks for creating and manipulating an employee table, demonstrating various SQL operations like adding columns, querying data, and modifying table structures. The chapter emphasizes the importance of clauses like WHERE and commands like ALTER TABLE in managing database tables.

Uploaded by

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

Chapter 2: SQL in Action Worksheet

1. What is the purpose of the CREATE TABLE command?


2. Which SQL command is used to add a new row of data to an existing table?
3. What is the role of the WHERE clause in an SQL query?
4. How is the UPDATE command different from the INSERT command?
5. What is the function of the ALTER TABLE command in SQL?
What is the difference between DROP TABLE and DELETE in SQL?
6. Write the correct SQL commands based on the following tasks.
a. Create a table named employee with the fields: EmpName (text), EmpID (number), and Department (text).
b. Add a new column Location (text) to the employee table.
c. Insert the following records into the table:
Ravi, 501, HR, Chennai
Aisha, 502, Finance, Mumbai
Kavi, 503, Finance, Bengaluru
d. Display all columns and rows from the employee table.
e. Display only the EmpName and EmpID of all employees.
f. Show only the employees whose names start with "A".
g. Show employees whose Location is "Mumbai".
h. Update the EmpID of employee "Ravi" to 601.
i. Delete the records of all employees whose names start with "A".
j. Display names of employees whose names end with the letter "i".
k. Multiply the EmpID of each employee by 5 and display the result along with their names.
l. Rename the column Location to Office.
m. Change the data type of the Office column to VARCHAR(20).
n. Display all employees who either belong to the HR department or whose names start with "R"
o. To display the structure of an existing table.
p. Delete the Office column from the table.
q. Delete the entire employee table.

Chapter 2: SQL in Action Worksheet


7. What is the purpose of the CREATE TABLE command?
8. Which SQL command is used to add a new row of data to an existing table?
9. What is the role of the WHERE clause in an SQL query?
10. How is the UPDATE command different from the INSERT command?
11. What is the function of the ALTER TABLE command in SQL?
What is the difference between DROP TABLE and DELETE in SQL?
12. Write the correct SQL commands based on the following tasks.
a. Create a table named employee with the fields: EmpName (text), EmpID (number), and Department (text).
b. Add a new column Location (text) to the employee table.
c. Insert the following records into the table:
Ravi, 501, HR, Chennai
Aisha, 502, Finance, Mumbai
Kavi, 503, Finance, Bengaluru
d. Display all columns and rows from the employee table.
e. Display only the EmpName and EmpID of all employees.
f. Show only the employees whose names start with "A".
g. Show employees whose Location is "Mumbai".
h. Update the EmpID of employee "Ravi" to 601.
i. Delete the records of all employees whose names start with "A".
j. Display names of employees whose names end with the letter "i".
k. Multiply the EmpID of each employee by 5 and display the result along with their names.
l. Rename the column Location to Office.
m. Change the data type of the Office column to VARCHAR(20).
n. Display all employees who either belong to the HR department or whose names start with "R"
o. To display the structure of an existing table.
p. Delete the Office column from the table.
q. Delete the entire employee table.

Common questions

Powered by AI

INSERT and UPDATE are both SQL commands, yet they serve different purposes. INSERT adds new rows of data to an existing table, as exemplified by "INSERT INTO employee (EmpName, EmpID, Department, Location) VALUES ('Ravi', 501, 'HR', 'Chennai');" which adds a new row . In contrast, UPDATE modifies existing records in the table; for example, "UPDATE employee SET EmpID = 601 WHERE EmpName = 'Ravi';" changes Ravi's EmpID to 601 .

The WHERE clause in SQL is used to filter records that meet a specified condition. This helps in retrieving only the necessary data from a database rather than all data available in a table. For instance, the query "SELECT * FROM employee WHERE Location = 'Mumbai';" will return all records from the employee table where the value in the Location column is 'Mumbai' .

ALTER TABLE is used to modify an existing table structure, for example, adding a new column or changing a data type, such as "ALTER TABLE employee ADD COLUMN Location TEXT;" to add a new column . DROP TABLE, however, is used to completely delete a table from the database and all its data, making it a more destructive action compared to ALTER TABLE .

In a scenario where you need to update the department of certain employees, the SQL UPDATE command can be combined with WHERE to target specific rows. For example, to change the department of employees from 'Finance' in Bengaluru to 'Marketing', the query would be "UPDATE employee SET Department = 'Marketing' WHERE Location = 'Bengaluru' AND Department = 'Finance';". This ensures only the specified rows are updated, maintaining the precision and accuracy of data changes .

The SQL command DROP TABLE removes the entire table, including its structure and all data within, from the database, which is irreversible without a backup . On the other hand, DELETE removes rows from a table without affecting the table's structure; for example, "DELETE FROM employee WHERE EmpName = 'Aisha';" deletes only the data specified by the condition . DROP TABLE is thus more severe than DELETE as it completely removes the table's existence.

To rename a column in SQL, the ALTER TABLE command along with the RENAME COLUMN clause is used. For instance, to rename the column Location to Office, the query would be "ALTER TABLE employee RENAME COLUMN Location TO Office;". This command not only changes the column name but requires an understanding of table permissions and a consideration of any dependencies within applications or queries relying on the original column name .

Indexing significantly enhances query performance by allowing faster data retrieval at the cost of additional storage and maintenance overheads. While indexes can speed up SELECT, WHERE, and ORDER BY operations by reducing search space, they also introduce complexity in write operations like INSERT, UPDATE, and DELETE as the indexes need updating. Trade-offs include increased storage requirements and potential performance degradation due to index maintenance, thus requiring careful selection and optimization based on query patterns and system architecture [Implicit knowledge based on database management].

PRIMARY KEY and UNIQUE constraints are crucial in maintaining data integrity. A PRIMARY KEY ensures each row in a table is unique and non-null, typically applied to an identifier column like EmpID. UNIQUE ensures all values in a column are distinct, useful for columns where duplicates aren't allowed, such as email addresses. Both constraints help prevent data anomalies and maintain database reliability, ensuring that operations like joins are efficient and meaningful [Implicit knowledge based on SQL standards].

Subqueries, or nested queries, are used to perform operations requiring multiple steps where the result of an inner query is used by an outer query. For instance, to find employees in departments with more than five members, one could use: "SELECT EmpName FROM employee WHERE Department IN (SELECT Department FROM employee GROUP BY Department HAVING COUNT(*) > 5);". This subquery approach allows breaking down complex requirements into manageable parts, improving performance and clarity [Implicit knowledge based on SQL standards].

SQL aggregate functions such as COUNT(), SUM(), and AVG() provide ways to perform calculations on a set of values, returning a single result. COUNT() returns the number of rows, SUM() calculates the total of a numerical column, and AVG() gives the mean value. For instance, to find the average salary in a table, you might use "SELECT AVG(Salary) FROM employee;". These functions are essential for statistical analysis and reporting [Implicit knowledge based on SQL standards].

You might also like