Scribd Logo
Upload

Welcome to Scribd!

  • 185 views

    Exercise - 1 SQL Basics - DDL

    Uploaded by

    anon_784586715
    This document contains instructions for two SQL exercises: 1. Create database schemas for students, courses, faculty and relationship tables with attributes and define primary and foreign keys. Perform CRUD operations like adding attributes, changing data types, inserting records, displaying tables. 2. Create tables for books, authors, and a relationship table with defined constraints. Write SQL queries to add, alter, drop constraints and perform CRUD operations on the tables including sorting and joining records.

    Copyright:

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

    Exercise - 1 SQL Basics - DDL

    Uploaded by

    anon_784586715
    185 views3 pages
    This document contains instructions for two SQL exercises: 1. Create database schemas for students, courses, faculty and relationship tables with attributes and define primary and foreign keys. Perform CRUD operations like adding attributes, changing data types, inserting records, displaying tables. 2. Create tables for books, authors, and a relationship table with defined constraints. Write SQL queries to add, alter, drop constraints and perform CRUD operations on the tables including sorting and joining records.

    Original Title

    Exercise_ 1 SQL Basics_DDL.docx

    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 contains instructions for two SQL exercises: 1. Create database schemas for students, courses, faculty and relationship tables with attributes and define primary and foreign keys. Perform CRUD operations like adding attributes, changing data types, inserting records, displaying tables. 2. Create tables for books, authors, and a relationship table with defined constraints. Write SQL queries to add, alter, drop constraints and perform CRUD operations on the tables including sorting and joining records.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    185 views3 pages

    Exercise - 1 SQL Basics - DDL

    Uploaded by

    anon_784586715
    This document contains instructions for two SQL exercises: 1. Create database schemas for students, courses, faculty and relationship tables with attributes and define primary and foreign keys. Perform CRUD operations like adding attributes, changing data types, inserting records, displaying tables. 2. Create tables for books, authors, and a relationship table with defined constraints. Write SQL queries to add, alter, drop constraints and perform CRUD operations on the tables including sorting and joining records.

    Copyright:

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

    Programme : M.

    S SE Semester : Summer I - 18
    Database Management Systems
    Course : Code : SWE304
    Lab
    Faculty : Prof. M. Premalatha Slot : L1+L4+L5+L8+L9+L11

    Ex. No. 1
    SQL Basics

    1. Create the following schemas by setting the primary and foreign


    keys
    student(regno, sname, cgpa)
    course(ccode, cname, credits)
    faculty(empid, fname, designation)
    course_student(regno, ccode)
    course_faculty(fid, ccode)
    2. Add email id attribute to student relation and describe the
    structure
    3. Change the data type of sname in student relation and describe
    the structure
    4. Change the size of any attribute and describe the structure
    5. Add phone number attribute to student relation and describe the
    structure
    6. Rename the empid attribute as fid in faculty relation and
    describe the structure
    7. Delete the email id attribute from student and describe the
    structure
    8. Insert 5 records each to all the schemas
    9. Display the contents of all the schemas
    10. Copy the contents of student table and name the table as
    student new.
    11. Delete the contents of student new table, describe the
    structure and display the contents of the table
    12. Delete the structure of student new table , describe the
    structure and display the contents of the table

    Ex. No. 2

    SQL - Integrity Constraints and DML

    Create the following tables and apply constraints as follows


    1. Books Table:
    a. isbn – primary key
    b. title - unique
    c. price – not null
    d. qty – not null
    2. Authors Table:
    a. authorId – primary key
    b. email
    3. Book_Authors:
    a. isbn – foreign key references books table
    b. authorId – foreign key references authors table

    Write SQL Queries for the following:

    1. Constraints:
    a. Add not null constraint to title in books table
    b. Add unique constraint to email in authors table
    c. Alter not null constraint in price attribute in books
    table and set the check constraint so that value is
    greater than 0.0
    d. Drop not null constraint in books table - qty
    attribute
    e. Set a default value of qty in books table as 0
    f. Drop any one foreign key constraint.
    2. DML
    a. List the authorid without repetition from book_authors
    b. Update the email of an author
    c. Insert records as given in the above tables
    d. Increase the price of all books with 10% of its price
    e. Sort the authors table in ascending order based on
    their names
    f. Sort the books table by price in descending and qty in ascending order.

    You might also like