Database Management Quiz
16 Questions
1 Views

Choose a study mode

Play Quiz
Study Flashcards
Spaced Repetition
Chat to lesson

Podcast

Play an AI-generated podcast conversation about this lesson

Questions and Answers

What is the correct relationship between database schema and instance?

  • Instance refers to the design of the database
  • Schema defines the physical data
  • Instance can only contain structural information
  • Schema is the logical design of the database, while instance is a snapshot of data (correct)

    • Which attribute is most suitable to be a primary key for student data?

  • Street
  • Name
  • Department
  • Id (correct)

    • Which statement correctly describes the role of a foreign key?

  • It must always reference a primary key from another relation (correct)
  • It can reference a candidate key in its own relation
  • It is optional and not necessary for database integrity
  • It can only link two relations with the same primary key

    • What type of SQL statement is CREATE TABLE?

    <p>Data Definition Language (DDL)</p> Signup and view all the answers

    Which of the following queries can be used to replace a join operation accurately?

    <p>Select name, course_id from instructor natural join teaches</p> Signup and view all the answers

    What would the output be for the SQL query SELECT * FROM employee WHERE salary>10000 AND dept_id=101?

    <p>All columns of the employee relation</p> Signup and view all the answers

    Which SQL statement contains a syntax error?

    <p>SELECT id WHERE id = 119 AND name = ‘Jane’;</p> Signup and view all the answers

    In the SQL statement LIKE '_____ Computer Engineering', what character fits in the blank?

    <p>%</p> Signup and view all the answers

    What result does the query display after execution: (SELECT emp_id FROM employee WHERE dept = 'Toys') EXCEPT (SELECT emp_id FROM employee WHERE dept = 'Cars')?

    <p>Tuples from the first part but not tuples from the second part</p> Signup and view all the answers

    What placeholder is used in an aggregate expression to eliminate duplicates in the query SELECT COUNT (____ ID)?

    <p>Distinct</p> Signup and view all the answers

    What should be used to represent missing phone numbers in a relation?

    <p>Null</p> Signup and view all the answers

    Which statement correctly describes the requirement for a primary key attribute?

    <p>Both Unique and Not null</p> Signup and view all the answers

    What type of join allows for inclusion of rows that do not have matching values?

    <p>Outer join</p> Signup and view all the answers

    Which command is used to delete all entries in a relation but retain its structure?

    <p>Delete from instructor;</p> Signup and view all the answers

    Which statement correctly describes how to insert a tuple from another relation?

    <p>INSERT INTO instructor SELECT ID, name, dept name, 18000 FROM student WHERE dept name = 'Music' AND tot cred &gt; 144;</p> Signup and view all the answers

    What syntax is correct for declaring a view named 'v'?

    <p>Create view v as 'query expression';</p> Signup and view all the answers

    Study Notes

    Multiple Choice Questions

    • Q1: Database instance is a snapshot of current data, while schema designs the logical structure.
    • Q2: Student ID is most likely the primary key for a student data table.
    • Q3: An attribute can be a foreign key if it references a primary key in another relation.
    • Q4: CREATE TABLE student (name VARCHAR, id INTEGER) is a Data Definition Language (DDL) statement.
    • Q5: SELECT name, course_id FROM instructor, teaches WHERE instructor_ID = teaches_ID; can be replaced by SELECT name, course_id FROM instructor NATURAL INNER JOIN teaches;
    • Q6: All columns (id, name, salary, dept_id) are displayed if SELECT * FROM employee WHERE salary>10000 AND dept_id=101; is executed.
    • Q7: Select id where id = 119 and name = 'Jane'; is the only statement with possible error.
    • Q8: %Computer Engineering should be inserted into the blank to select the department with "Computer Engineering" as the ending string.
    • Q9: SELECT name FROM instructor WHERE salary BETWEEN 90000 AND 100000; can replace the query to select instructor's names from a salary range.
    • Q10: Union operation automatically eliminates duplicated tuples and adds other tuples.
    • Q11: The query (SELECT emp_id FROM employee WHERE dept = 'Toys') EXCEPT (SELECT emp_id FROM employee WHERE dept = 'Cars') retrieves employee IDs from the Toys department that are not present in the Cars department.
    • Q12: Missing phone numbers in a relation are represented as NULL values.
    • Q13: A primary key is both unique and not null.
    • Q14: To eliminate duplicates when counting, use the DISTINCT keyword.
    • Q15: The SQL expression for "greater than at least one" is > some.
    • Q16: DELETE FROM instructor; deletes all rows from the instructor table, preserving the table structure.
    • Q17: SQL INSERT statements can copy data from other tables.
    • Q18: Outer joins include rows even if there's no match in the other table.
    • Q19: Use CREATE VIEW v AS "query expression";; to create view v.
    • Q20: Integrity constraints like CHECK(budget >= 0) ensure non-negative budget values.
    • Q21: CREATE TABLE course...FOREIGN KEY (dept_name) REFERENCES department... is a relational database design, defining a foreign key constraint that ensures data consistency between the course and department tables.
    • Q22: The DROP TABLE command is used to remove a table from a database.
    • Q23: To enforce a column to hold a value, Not null constraint should be applied.
    • Q24: CREATE VIEW v AS "query expression"; defines a view in SQL.
    • Q25: ON DELETE CASCADE specifies that deletion of a tuple in the parent table automatically deletes the related tuples in the child table.
    • Q26: Set difference ( - ) is the correct operator that gives the distinct.
    • Q27: π C, D (S) ∩ π A, B (R) returns the intersection of the C and D columns from S, and the A and B columns from R.
    • Q28: π B (S) - π B (R) finds the values in column B of table S that are not also in column B of table R.
    • Q29: SQL's SELECT clause corresponds to the relational algebra operation σ. (selection)
    • Q30: SQL query SELECT a,b FROM r NATURAL INNER JOIN s WHERE r.A=2 matches relational algebra expression σr.A=2(rxs).
    • Q31: Πbranch_name (σ branch_city = "London"(branch)) is used to retrieve branch names located in London.
    • Q32: Π customer_name (σ amount > 500(loan x customer)) is used to retrieve names of customers who have a loan amount larger than 500.
    • Q33: P (σcustomer_city="Paris"(customer)) finds customers residing in Paris, and returns the street and count along with customer name.
    • Q34: Π customer_name( σ depositor.customer_name = borrower.customer_name(R)) retrieves customer names having both a loan and an account.
    • Q35: Πbranch_name σ max (balance)(account)) is the query used to identify the branch name where the largest balance value is located.
    • Q36: To increment loan amounts by 20% Π loan_number, branch_name, amount* 1.2(loan) is used.
    • Q37: R - σ branch_name = "Palm Springs”(S) is used to delete accounts from a branch named Palm Springs.
    • Q38: The question is about composite primary keys. Customers can have multiple accounts, an account can be shared among multiple customers, and accounts can be present in several branch locations.
    • Q39: Foreign key definitions involve referencing another table's primary key to maintain data integrity.
    • Q40: SELECT staff_no, salary FROM Staff WHERE salary BETWEEN 100 AND 200 ORDER BY salary DESC retrieves staff numbers and salaries within the 100-200 range in descending order. (inclusive).
    • Q41: SELECT count(distinct car_no) FROM rented WHERE rentDate BETWEEN '1-Jan-2021' AND '31-Jan-2021'counts distinct cars rented in Jan 2021.
    • Q42: SELECT compNo, count(staff_no), SUM(salary) FROM Staff GROUP BY compNo HAVING count(staff_no) > 15; finds companies with more than 15 staff and their total salary.
    • Q43: SELECT custNo, fName, IName FROM customer LEFT OUTER JOIN rented ON customer.custNo = rented.custNo finds all customers with associated rental information or displays customers without any rentals.
    • Q44: SELECT playername FROM player WHERE playerid NOT IN (SELECT playerid FROM player_record) is used to find all players who haven't played in any match.
    • Q45: SELECT refname, ref_age FROM referee WHERE league = 'Premier League' ORDER BY ref_age finds the youngest referees in the Premier League.
    • Q46: UPDATE stock SET quantity = quantity - 100 WHERE code = 'A1' decrements the quantity of items with code 'A1' by 100 units in the stock table.
    • Q47: DELETE FROM stock WHERE code = 'A1' removes all rows with code 'A1' from the stock table.

    Studying That Suits You

    Use AI to generate personalized quizzes and flashcards to suit your learning preferences.

    Quiz Team

    Related Documents

    CMPE353/CMSE351 Midterm Study Questions PDF

    Description

    Test your knowledge on database management concepts with this multiple-choice quiz. Topics include database instances, primary and foreign keys, and SQL statements. Perfect for students studying Database Systems.

    More Like This

    Use Quizgecko on...
    Browser
    Open
    Browser
    Browser