SQL problem-1
I am an engineering student and a tech freak I love reading tech articles and blogs I am here to read such blogs and share some with others who have my interest 😊
Ram is working in an XYZ company since he is a SQL developer his boss told him to test whether the CRUD(Create Read Update Delete) operation is functional or not.
Data given by the boss is :
Database Name: Gym Report
Table Name: Gym Survey
Columns In Table: rid, user_name, email, age, does_gym, total_hours_in_gym
So, Ram first created the schema with the following commands:
CREATE SCHEMA gym_report ;
CREATE TABLE gym_report.gym_survey (
rid INT NOT NULL,
user_name VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL,
age INT NULL,
does_gym VARCHAR(45) NULL,
total_hours_in_gym INT NOT NULL,
UNIQUE INDEX rid_UNIQUE (rid ASC) VISIBLE,
UNIQUE INDEX email_UNIQUE (email ASC) VISIBLE,
UNIQUE INDEX user_name_UNIQUE (user_name ASC) VISIBLE);
Oh No!!! Ram forgot to add the Primary key let's alter and add it:
ALTER TABLE gym_report.gym_survey
ADD PRIMARY KEY (rid);
Now, he decides to add the data :
use gym_report;
INSERT INTO gym_survey(rid,user_name,email,age,does_gym,total_hours_in_gym)
VALUES(345,'Abhilash Khanna','khannaabhi@yahoo.com',23,'Yes',6),
(687,'Ayush Rastogi','ayu.rastogi@yahoo.com',28,'Yes',5),
(647,'Luna Luna','luna@gmail.com',20,'No',1),
(9876,'Asish nayak','nayakashi@hotmail.com',25,'Yes',2),
(497,'Nisha Singh','nishasingh@gmail.com',24,'Yes',3),
(3476,'Ravi Rathore','rathoreravi@rediff.com',28,'No',1);
Then, it's time to read the data:
SELECT FROM gym_survey;
Now, let's do a bit different thing update the table and check it works
UPDATE gym_survey SET total_hours_in_gym=1 WHERE rid=9876;
UPDATE gym_survey SET does_gym='No' WHERE rid=9876;
SELECT FROM gym_survey;
Now, let's try to check if does_gym could be studied in any other way because if the person does gym for less than 2 hours then he/she would not be considered:
SELECT rid,user_name,email,age,total_hours_in_gym,
CASE
WHEN total_hours_in_gym >= 2 THEN 'Yes'
ELSE 'No'
END AS does_gym
FROM gym_survey;
Since it works then the does table column doesn't make any requirement so just delete it from the table:
ALTER TABLE gym_survey
DROP COLUMN does_gym;