Lab Sessions All Questions
In this lab we are discuss about
Note: Before finding all LAB SESSIONS QUESTIONS, Create two tables (Create_1 and Create_2) then find.
Q1. Find all the players who were present in the test match 1 or test match?
Q2. Write a MySQL query to find the players from the test match1 having popularity higher than the average popularity.
Ans: Select player_name, popularity from cricket_1
WHERE
popularity>(SELECT AVG (popularity ) from Cricket_1);
Q3. Find player_id and player_name that are common in the test match 1 and test match 2.
Ans: Select player_id, player_name from cricket_1 WHERE cricket_1.player_id In (Select player_id from Cricket_2);
Q4. Retrieve player_id, runs, and player_name from cricket_1 table and display list of the players where the runs are more than the average runs.
Ans: Select player_id, runs, player_name from cricket_1 WHERE run>(SELECT avg(runs) from cricket_1);
Q5. Write a query to extract the player_id, runs, and Player_name from the table “cricket_1” where the runs are greater than 50.
Ans: Select player_id, run , player_name from cricket_1, where runs>50;
Q6. Write a query to extract all the columns from cricket_1, where player_name starts with “y” and ends with “v”.
Ans: Select *from cricket_1 WHERE player_name LIKE ‘y%v’;
Q7. Write a query to extract all the columns from cricket_1, where player_name does not end with “t”.
Ans: Select *from cricket_1 WHERE player_name NOT LIKE ‘%t’;
Q8. Extract the player_id and player_name of the player where the charisma value is NULL.
Ans: SELECT player_id, player_name from new_cricket WHERE charisma IS NULL;
Q9. Separate the all player_id into single numeric ids(example PL1=1)
Ans: SELECT player_id, SUBSTR(player_id,3) from new_cricket;
Q10. Write a MYSQL query to extract player_id, player_name and charisma where the charisma is greater than 20.
Ans: SELECT player_id. Player_name, charisma from new_cricket where
Part 1: Now Complete
Question 1: Create a Databases bank.
Solution : CREATE DATABASES BANK;
Use Bank;
Question 2: Create a table with the name “bank_details” with the following columns.
Product with string data types
Quantity with real number data type
Price with real number data type
Purchase_cost with decimal data type
Estimated_sale_price with data type float
Solution:
create table bank_details(
Product char(10),
Quantity int,
Price real,
Purchase_cost decimal(6,2),
Estimated_sale_price float
);
Question 3: Display all columns and their datatypes and size in Bank_Details
Solution : Describe bank_details;
Question 4: Insert two records into bank_details.
Solution:
1st records with values:
Product: PayCard
Quantity: 3
Price: 330
Purchase_Cost: 8008
Estimated_Sale_Price: 9090
Product: PayPoints
Quantity: 4
Price: 200
Purchase_Cost: 8000
Estimated_Sale_Price: 6800
Solution :
insert into bank_details value('paycard',3,330,8080,9090);
insert into bank_details value('paypoint','4','200',6800,8985);
select *from bank_details;
Question 5: Add a column Locations to the existing Bank_Details tables with data type varchar and size(20).
Ans: alter table bank_details add column Location varchar(20);
Question 6: What is the value of Location for product : “PayCard”
Ans: select location from bank_details where product='PayCard';
Question 7: How many characters does the product: “PayCard” have in the Bank_details table.
Ans: SELECT char_length(product) from bank_details where product=”PayCard”;
Questions 8: Alter the product filed from char to var-char in bank_details.
Question 9: Reduce the size of the product filed from 10 to 6 check if it is possible.
#Error because char value max=8,9 so you can’t change the var-char value in (6).
Question 10. Create a table named as a bank_holidays with below fields?
Holidays fields which displays only date
Start_time field which displays hours and minutes
End_time fields also displays hours and minutes and timezone
Solution:
create table bank_Holiday(
Holiday time,
Start_time datetime,
End_time timestamp
);
Question 11.
Step 1: insert today’s date details in all fields in bank_Holidays.
Step 2: After step 1, perform the below
Step 3: Postpone Holidays to next day by updating the Holidays Fields
Solution :
Step 1:
insert into bank_holiday value(current_date(),
current_date(),
current_date()
);
Step 2:
update bank_holidays set holiday= date_add(holiday, interval 1 day);
Question 12: Update the End_time with current European time.
Solution:
Update bank_holiday set end_time =utc_timestamp();
Question 13: Display output of product filed as new_products in bank_details table
Solution :
select product as new_product from bank_details;
Questions 14: Display only one records from bank_details.
Ans:
Solution :
select *from bank_details limit 2;
select substr(Location, 1, 5) from bank_details;
/*use bank;
create table bank_details(
Product char(10),
Quantity int,
Price real,
Purchase_cost decimal(6,2),
Estimated_sale_price float
);
*/
#insert into bank_details value('paycard',3,330,8080,9090);
#insert into bank_details value('paypoint','4','200',6800,8985);
#alter table bank_details add column Location varchar(20);
#select location from bank_details where product='PayCard';
#SELECT char_length(product) from bank_details where product="PayCard";
#alter table bank_details modify product varchar(10);
#Alter table bank_details modify product varchar(6);
/*
create table bank_Holiday(
Holiday date,
Start_time datetime,
End_time timestamp
);
*/
/*
insert into bank_Holiday value(
current_date(),
current_date(),
current_date()
);
*/
#update bank_holidays set holiday= date_add(holiday, interval 1 day);
#update bank_holiday set end_time =utc_timestamp();
#select product as new_product from bank_details;
#select *from bank_details limit 2;
select substr(Location, 1, 5) from bank_details;
#select *from bank_holiday;
Hope this is all question is useful and now follow me for more .. thanks
0 Comments