Scribd Logo
Upload

Welcome to Scribd!

  • 3K views

    Class XI Practical Assignment Mysql

    Uploaded by

    harsh
    This document provides examples of SQL queries and commands to practice on sample tables. It includes queries to select, filter, sort, update and manipulate data. Sample tables include Charity, Grocer, Gym, Employee, Department, Loans and Teacher. Queries cover concepts like WHERE, BETWEEN, IN, LIKE, JOIN, NULL, DISTINCT, ORDER BY, UPDATE, DELETE, ALTER etc. It also provides output for queries on Loan_Accounts table.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

    Class XI Practical Assignment Mysql

    Uploaded by

    harsh
    3K views6 pages
    This document provides examples of SQL queries and commands to practice on sample tables. It includes queries to select, filter, sort, update and manipulate data. Sample tables include Charity, Grocer, Gym, Employee, Department, Loans and Teacher. Queries cover concepts like WHERE, BETWEEN, IN, LIKE, JOIN, NULL, DISTINCT, ORDER BY, UPDATE, DELETE, ALTER etc. It also provides output for queries on Loan_Accounts table.

    Original Title

    Class XI practical assignment mysql

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document provides examples of SQL queries and commands to practice on sample tables. It includes queries to select, filter, sort, update and manipulate data. Sample tables include Charity, Grocer, Gym, Employee, Department, Loans and Teacher. Queries cover concepts like WHERE, BETWEEN, IN, LIKE, JOIN, NULL, DISTINCT, ORDER BY, UPDATE, DELETE, ALTER etc. It also provides output for queries on Loan_Accounts table.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    3K views6 pages

    Class XI Practical Assignment Mysql

    Uploaded by

    harsh
    This document provides examples of SQL queries and commands to practice on sample tables. It includes queries to select, filter, sort, update and manipulate data. Sample tables include Charity, Grocer, Gym, Employee, Department, Loans and Teacher. Queries cover concepts like WHERE, BETWEEN, IN, LIKE, JOIN, NULL, DISTINCT, ORDER BY, UPDATE, DELETE, ALTER etc. It also provides output for queries on Loan_Accounts table.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    of 6

    Class XI

    INFORMATICS PRACTICES(065)
    MySQL Practical Questions

    1. Create the following table named "Charity" and write SQL queries for the tasks that follow:

    Table: Charity

    (Contribution is in Rs.)

    a) Display all first names in lowercase.


    b) Display all last names of people of Mumbai city in uppercase.
    c) Display Person Id along with First 3 characters of his/her name.
    d) Display first name concatenated with last name for all the employees.
    e) Display length of address along with Person Id.
    f) Display last 2 characters of City and Person Id.
    g) Display Last Names and First names of people who have "at" in the second or third position in their
    first names.
    h) Display the position of 'a' in Last name in every row.
    i) Display Last Name and First name of people who have "a" as the last character in their First names.
    j) Display the first name and last name concatenated after removing the leading and trailing blanks.
    k) Display Person Id, last names and contribution rounded to the nearest rupee of all the persons.
    l) Display Person Id, last name and contribution with decimal digits truncated of all the persons.
    m) Display Last name, contribution and a third column which has contribution divided by 10.Round it to
    two decimal points.

    2. Consider the table "Grocer" and write SQL queries for the tasks that follow:

    Table: Grocer

    (Unit Price is per kg price)


    a) Display Item name, unit price along with Date of purchase for all the Items.
    b) Display Item name along with Month (in number) when it was purchased for all the items.
    c) Display Item name along with year in which it was purchased for all the items.
    d) Display Item Id, Date of Purchase and day name of week (e.g. Monday) on which it was purchased
    for all the items.
    e) Display names of all the items that were purchased on Mondays or Tuesdays.
    f) Display the day name of the week on which Rice was purchased.
    g) Display the Item name and unit price truncated to integer value (no decimal digits) of all the items.
    h) Display current date.

    3. Write SQL statements to do the following:


    a) Using the three separate words "We," "study," and "MySQL," produce the following output:
    "We study MySQL"
    b) Use the string "Internet is a boon" and extract the string "net".
    c) Display the length of the string "Informatics Practices".
    d) Display the position of "My" in "Enjoying MySQL".
    e) Display the name of current month.
    f) Display the date 10 years from now. Label the column "Future."
    g) Display the day of week on which your birthday will fall or fell in 2010.

    4. Consider the following table named "GYM" with details about Fitness products being sold in
    the store.
    Table Name : GYM
    PrCode stores Codes of Products
    PrName stores names of Products
    (UnitPrice is in Rs.)

    Write SQL statements to do the following:


    a) Display the names of all the products in the store.
    b) Display the names and unit price of all the products in the store.
    c) Display the names of all the products with unit price less than Rs.20000.00
    d) Display details of all the products with unit price in the range 20000 to 30000
    e) Display names of all products by the manufacturer "Fit Express".
    f) Display all rows sorted in descending order of unit price.
    g) Add a new row for product with the details: "P106","Vibro Exerciser", 23000, manufacturer : "Avon
    Fitness".
    h) Change the Unit Price data of all the rows by applying a 10% discount reduction on all the products.
    i) Display details of all products with manufacturer name starting with "A".
    5. Consider the following tables Employee and Department.

    Write SQL statements to do the following:


    a) Display the last names and first names of all employees.
    b) Display the Department names of all employees, without duplicates.
    c) Display all the details of employees with last name as "Lakshmi".
    d) Display all the details of employees whose last name is ""Rajlani" or "Sharma".
    e) Display the codes and first names of all employees of 'Accounts' department.
    f) Display department names of departments with budget above 18000.
    g) Display all the details of employees whose First name begins with "S".
    h) Display department details(from Department table) in descending order of Budget amount.
    i) Change the Department name "Sales" to "Marketing" everywhere in the table "Employee" and
    "Department"
    j) Add a new row with appropriate data values in Department table.
    k) Create the table Department with columns of appropriate data types.

    6. Consider a database LOANS with the following table:


    Write SQL commands for the tasks 1 to 35 and write the output for the SQL commands 36 to 40:
    Create Database and use it
    1. Create the database LOANS.
    2. Use the database LOANS.
    Create Table / Insert Into
    3. Create the table Loan_Accounts and insert tuples in it.
    Simple Select
    4. Display the details of all the loans.
    5. Display the AccNo, Cust_Name, and Loan_Amount of all the loans.
    Conditional Select using Where Clause
    6. Display the details of all the loans with less than 40 instalments.
    7. Display the AccNo and Loan_Amount of all the loans started before 01-04-2009.
    8. Display the Int_Rate of all the loans started after 01-04-2009.

    Using NULL
    9. Display the details of all the loans whose rate of interest is NULL.
    10. Display the details of all the loans whose rate of interest is not NULL.
    Using DISTINCT Clause
    11. Display the amounts of various loans from the table Loan_Accounts. A loan amount should appear
    only once.
    12. Display the number of instalments of various loans from the table Loan_Accounts. An instalment
    should appear only once.
    Using Logical Operators (NOT, AND, OR)
    13. Display the details of all the loans started after 31-12-2008 for which the number of instalments
    are more than 36.
    14. Display the Cust_Name and Loan_Amount for all the loans which do not have number of
    instalments 36.
    15. Display the Cust_Name and Loan_Amount for all the loans for which the loan amount is less than
    500000 or int_rate is more than 12.
    16. Display the details of all the loans which started in the year 2009.
    17. Display the details of all the loans whose Loan_Amount is in the range 400000 to 500000.
    18. Display the details of all the loans whose rate of interest is in the range 11% to 12%.
    Using IN Operator
    19. Display the Cust_Name and Loan_Amount for all the loans for which the number of instalments are
    24, 36, or 48. (Using IN operator)
    Using BETWEEN Operator
    20. Display the details of all the loans whose Loan_Amount is in the range 400000 to 500000. (Using
    BETWEEN operator)
    21. Display the details of all the loans whose rate of interest is in the range 11% to 12%. (Using
    BETWEEN operator)
    Using LIKE Operator
    22. Display the AccNo, Cust_Name, and Loan_Amount for all the loans for which the Cust_Name ends
    with 'Sharma'.
    23. Display the AccNo, Cust_Name, and Loan_Amount for all the loans for which the Cust_Name ends
    with 'a'.
    24. Display the AccNo, Cust_Name, and Loan_Amount for all the loans for which the Cust_Name
    contains 'a'
    25. Display the AccNo, Cust_Name, and Loan_Amount for all the loans for which the Cust_Name does
    not contain 'P'.
    26. Display the AccNo, Cust_Name, and Loan_Amount for all the loans for which the Cust_Name
    contains 'a' as the second last character.
    Using ORDER BY clause
    27. Display the details of all the loans in the ascending order of their Loan_Amount.
    28. Display the details of all the loans in the descending order of their Start_Date.
    29. Display the details of all the loans in the ascending order of their Loan_Amount and within
    Loan_Amount in the descending order of their Start_Date.
    Using UPDATE, DELETE, ALTER TABLE
    30. Put the interest rate 11.50% for all the loans for which interest rate is NULL.
    31. Increase the interest rate by 0.5% for all the loans for which the loan amount is more than 400000.
    32. For each loan replace Interest with (Loan_Amount*Int_Rate*Instalments) 12*100.
    33. Delete the records of all the loans whose start date is before 2007.
    34. Delete the records of all the loans of 'K.P. Jain'
    35. Add another column Category of type CHAR(1) in the Loan table.

    Find the Output of the following queries


    36. SELECT cust_name, LENGTH(Cust_Name), LCASE(Cust_Name), UCASE(Cust_Name) FROM
    Loan_Accounts WHERE Int_Rate < 11.00;
    37. SELECT LEFT(Cust_Name, 3), Right(Cust_Name, 3), SUBSTR(Cust_Name, 1, 3) FROM
    Loan_Accounts WHERE Int_Rate > 10.00;
    38. SELECT RIGHT(Cust_Name, 3), SUBSTR(Cust_Name, 5) FROM Loan_Accounts;
    39. SELECT DAYNAME(Start_Date) FROM Loan_Accounts;
    40. SELECT ROUND(Int_Rate*110/100, 2) FROM Loan_Account WHERE Int_Rate > 10;
    Write the output produced by the following SQL commands:
    41. SELECT POW(4,3), POW(3,4);
    42. SELECT ROUND(543.5694,2), ROUND(543.5694), ROUND(543.5694,-1);
    43. SELECT TRUNCATE(543.5694,2), TRUNCATE(543.5694,-1);
    44. SELECT LENGTH("Prof. M. L. Sharma");
    45. SELECT CONCAT("SHEIKH", " HAROON") "FULL NAME";
    46. SELECT YEAR(CURDATE()), MONTH(CURDATE()), DAY(CURDATE());
    47. SELECT DAYOFYEAR(CURDATE()), DAYOFMONTH(CURDATE()), DAYNAME(CURDATE());
    48. SELECT LEFT("Unicode",3), RIGHT("Unicode",4);
    49. SELECT INSTR("UNICODE","CO"), INSTR("UNICODE","CD");
    50. SELECT MID("Informatics",3,4), SUBSTR("Practices",3);

    7. Write SQL commands for the following on the basis of given table relation Teacher.
    a) To show all information about the teacher of history department.
    b) To list the names of female teachers who are in Hindi department.
    c) To list names of all teachers with their date of joining in ascending order.

    8. Consider the Empl table and write SQL command to get the following:

    a) Write a query to display EName and Sal of employees whose salary are greater than or equal to
    2200?
    b) Write a query to display details of employs who are not getting commission?
    c) Write a query to display employee name and salary of those employees who don’t have their
    salary in range of 2500 to 4000?
    d) Write a query to display the name, job title and salary of employees who don’t have manager?
    e) Write a query to display the name of employee whose name contains “A” as third alphabet?
    f) Write a query to display the name of employee whose name contains “T” as last alphabet?
    g) Write a query to display the name of employee whose name contains ”M” as First and “L” as
    third alphabet?
    h) Write a query to display details of employees with the text “Not given”, if commission is null?

    You might also like