SQL assignment that covers the basic usage of the INSERT, UPDATE, and DELETE statements.

Pankaj-Str commented 1 year ago

Scenario: Assume you have a database named Company with a table named Employees. The Employees table has the following columns:

EmployeeID (integer, primary key)
FirstName (varchar)
LastName (varchar)
Department (varchar)
Salary (numeric)

Tasks:

Insert Data:
- Insert a new employee with the following details:
  - EmployeeID: 101
  - FirstName: 'John'
  - LastName: 'Doe'
  - Department: 'IT'
  - Salary: 60000
Update Data:
- Update the salary of the employee with EmployeeID 101 to 65000.
Delete Data:
- Delete the employee with EmployeeID 101 from the Employees table.

Submission Guidelines:

Create a SQL script that includes the necessary INSERT, UPDATE, and DELETE statements.
Include comments to explain each statement.
Assume that the database and table already exist.

Sample Solution:

-- Task 1: Insert Data
INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary)
VALUES (101, 'John', 'Doe', 'IT', 60000);

-- Task 2: Update Data
UPDATE Employees
SET Salary = 65000
WHERE EmployeeID = 101;

-- Task 3: Delete Data
DELETE FROM Employees
WHERE EmployeeID = 101;

deepanshup123 commented 12 months ago

-- Using the database 
USE COMPANY;

-- Inserting the value in the created table
INSERT INTO EMPLOYEES(EmployeeID, FirstName, LastName, Department, Salary)
VALUES(101, ‘John’, ‘Deo’, ‘IT’, 60000 );

-- Updating the table data
UPDATE Employee
SET Salary  = 65000
WHERE EmployeeID = 101;

-- Deleting the table data
DELETE FROM Employee
WHERE EmployeeID = 101;

Pankaj-Str commented 12 months ago

Submission Guidelines :

Create a SQL script that includes the necessary INSERT, UPDATE, and DELETE statements. Include comments to explain each statement. Assume that the database and table already exist.

mannasiii commented 11 months ago

create database work;

use work; 

Create table company(
Employee_id int,
first_name varchar (20),
last_name varchar (10),
deparment varchar (20),
salary int
);

describe company;

--  Task 1: Insert Data
insert into   company ( Employee_id , first_name, last_name,  deparment,  salary)
VALUES (101, 'John', 'Doe', 'IT', 60000);

insert into   company ( Employee_id , first_name, last_name,  deparment,  salary)
values (102, 'Manasi', 'rao', 'ceo', '3000000');

insert into   company ( Employee_id , first_name, last_name,  deparment,  salary)
values (103, 'sam', 'dalgona', 'producer', '35000');

select * from company;

-- truncate company;
-- Task 2: Update Data

-- Updating salary for Employee_id = 101
update company
set salary = 65000
where Employee_id = 101;

DELETE FROM company
WHERE Employee_id  = 103;

shital9003 commented 11 months ago

-- Show existing databases
show databases;

-- Create Database
create database company;

-- Select Database
USE company;

-- Create Employees Table
CREATE TABLE Employees(
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(10),
LastName VARCHAR(10),
Contact_Number INT,
Address VARCHAR(25),
D_O_B DATE,
Job_title VARCHAR(20),
Department VARCHAR(15),
Salary NUMERIC
);

**-- Task 1: Insert Data**
-- Inserting a new employee with EmployeeID 101, FirstName 'John', LastName 'Doe', Department 'IT', and Salary 60000.
INSERT INTO Employees 

values (101, "John", "Doe", 9874563210, "borivali", 1993/12/02 ,".net Developer", "IT", 60000),
       (102, "vyas", "hum", 9632587410, "andheri", 1980/11/15 ,"Marketing Manager", "Marketing", 100000),
       (103, "ram", "shyam", 9889587410, "bandra", 1985/17/07 ,"Production Supervisor", "production", 70000);

-- check table 
select * from Employees;       

**-- Task 2: Update Data**
-- Updating the salary of the employee with EmployeeID 101 to 65000.

UPDATE Employees
SET Salary = 65000
WHERE EmployeeID = 101;

**-- Task 3: Delete Data**
-- Deleting the employee with EmployeeID 101 from the Employees table.

DELETE FROM Employees
WHERE EmployeeID = 101;   

-- Check the table after the deletion   
select * from Employees;

ADITYA33G commented 11 months ago

CREATE DATABASE COMPANY;

USE COMPANY;

CREATE TABLE Employees(
EmployeeID int primary key,
FirstName varchar(20),
LastName varchar(20),
Department varchar(20),
Salary numeric);

DESC Employees;

INSERT INTO Employees VALUE (101, 'John', 'Doe', 'IT', 60000 ) ;
INSERT INTO Employees VALUE (102, 'Aditya', 'Marsh', 'IT', 200000 ) ;
INSERT INTO Employees VALUE (103, 'Nimish', 'Stoinis', 'Designer', 450000 ) ;

UPDATE Employees
SET Salary = 65000
WHERE EmployeeID = 101;

DELETE FROM Employees
WHERE EmployeeID = 101;

SELECT * FROM Employees;

Anilkumar-andy commented 11 months ago


-- creation of database
CREATE DATABASE IF NOT EXISTS company;

-- selecting database
USE company;

-- creating table on temporary basis for current instance and assigning the range to store the values
CREATE TEMPORARY TABLE IF NOT EXISTS Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Department VARCHAR(25),
Salary NUMERIC);

-- to display thet table with datatypes 
DESC employees;

-- inseerting values in tables
INSERT INTO Employees VALUES(101,'John','Doe','IT',60000);

-- updatng with setting th limit to update the non of rows
UPDATE  Employees
    SET salary=65000
    WHERE EmployeeID=101
    ORDER BY EmployeeID
    LIMIT 1;

-- deleting specific employee data using employeeID
DELETE FROM Employees
WHERE EmployeeID = 101;

AnmolDixitB13 commented 6 months ago


create database Company ;
use Company

create table Employees(
EmployeeID int,
FirstName varchar (15),
LastName varchar(15),
Department varchar(15),
Salary float
) ;

select * from Employees ;

alter table Employees
alter column EmployeeID int not null ;
/*
For a column to be primary key, it must be non null. So, the nullability of the already existing 
column 'EmployeeID' has been changed suitably, i.e. the column has been made not null.
*/

alter table Employees
add constraint prim_id primary key(EmployeeID) ;
/*
Adding the constraint of primary key to the already existing column EmployeeID of the table
Employees.
*/

insert into Employees values(101, 'John', 'Doe', 'IT', 60000) ;

/*
Alternately, data can also be inserted in a column specific way as shown below:
insert into Employees(EmployeeID, FirstName, LastName, Department, Salary) values(101, 'John',
'Doe', 'IT', 60000) ;
*/

select * from Employees ;

update Employees
set Salary=65000 where EmployeeID=101 ;

delete from Employees
where EmployeeID=101 ;

/*
Alternately/similarly, salary (or any other column data) can also be modified by specifying some
other condition related to that row, such as Department, etc. However, it may not be that specific
or in other words the specificity of the operation would require greater attention because there 
may be more than 1 employees in a department, similarly more than 1 employees may exist with the
same set of names, so adding/removing/updating data is usually better & easier when done using a
unique identifier like employee id.
*/

armandevyas2001 commented 6 months ago

---Select database
USE company

---Create table employees with a table-level primary key constraint
CREATE TABLE employees (
EmployeeID int not null,
FirstName varchar(20),
LastName varchar(20),
Department varchar(20),
salary bigint,
CONSTRAINT pk_id PRIMARY KEY (EmployeeID)
);

---This SQL statement inserts a new employee into the 'employees' table with the specified details.
---The OUTPUT clause is used to return the details of the inserted employee.
---'inserted.*' in the OUTPUT clause selects all columns of the newly inserted row.

INSERT INTO employees(EmployeeID,FirstName,LastName,Department,salary)
OUTPUT inserted.* VALUES(101,'John','Doe','IT',60000);

---This SQL statement updates the 'salary' column of the 'employees' table.
UPDATE employees 
SET salary=65000 
WHERE EmployeeID=101;   

--- Selects all columns from the 'employees' table for the employee with EmployeeID 101.

SELECT * FROM employees 
WHERE EmployeeID=101;

----- Declares a table variable named @DeletedEmployees.
---This table variable is used to store the details of the deleted employees for further processing or logging.

BEGIN
DECLARE @DeletedEmployees TABLE (
    EmployeeID int,
    FirstName varchar(20),
    LastName varchar(20),
    Department varchar(20),
    Salary bigint
);

-- Delete the employee with EmployeeID = 101 and store the deleted row in @DeletedEmployees
DELETE FROM employees
OUTPUT deleted.EmployeeID, deleted.FirstName, deleted.LastName, deleted.Department, deleted.Salary
INTO @DeletedEmployees
WHERE EmployeeID = 101;

-- Output the details of the deleted employee
SELECT 'Employee Deleted:', * FROM @DeletedEmployees;

END;

Pankaj-Str / SQL-tutorial

SQL assignment that covers the basic usage of the INSERT, UPDATE, and DELETE statements. #1