Database Query and Update Database Using SQL | ITC 556

Home Assignment Answers Database Query and Update Database Using SQL | ITC 556

Several steps are necessary to be followed for this assignment:

  • Downloading Steps:
  1. go to official site of sqlite i.e.
  2. click on downloads
  3. click on
  4. it will download a zip file
  5. extract all and run the sqlite3
  6. it will open a command line window
  • Creating the database
  1. To create the database use command ‘.open database_name.db’.For example  I want to create database with ‘my_student_id.db’.So I will write the command as  ‘.open myid.db’.
  2. As I have to import the text file data to my database file, I have to write the read command that is ‘.read a5.txt’.
  3. After this command all data will be imported in ‘my_id.db’
  • Formatting the output(Optional)

.mode: The result of a sqlite query can be shown in eight different formats that are

  1. Csv
  2. Column
  3. Html
  4. Insert
  5. Line
  6. List
  7. Quote
  8. Tabs
  9. Tcl

The .mode command is used to use one of these formats. By default the output is displayed in the list format. To change we have to write the   ‘.mode ’ command  with an option one of these. As I want to format my output in the form of rows and columns that’s why I am using ‘.mode  column’.

.header: ‘.header ‘command is used with two options on and off.As I want to to display header in my output I am using the command ‘.headers on’

  • Displaying all tables

To show all tables i have used ‘.tables’ command.

Part A – DML

1.Locate the record in the vendor table that does not have a value for the attribute V_STATE

In first query it is asked to display the records of vendor table for which v_state column does have any value that mean v_state contains null. For that we have to use where clause to put the condition in for retrieving desired information.

  1. Find the customers whose balance is greater than $300 but less than $400.

For this query  we have to find the records in the customer table where balance is greater than 300 and less than 400.To achieve the result we can use and operator with two conditions or we can use between clause.

    3.Show the names of the customers for whom we have invoices.

Here we have to find the name of the customers  that have invoices. Fot that I have used join on two tables that are customer and invoice. It will display names of those customer only where customer code of customer table and invoice table  are equal.

  1. What is the max balance?

To display the maximum customer balance I have used an aggregate function max(). Max(cus_balance) will displays the maximum  value of cus_balance  column in customer table.

  1. Show the CUS_CODE of the customers for whom we DO NOT have invoices.

We can retrieve this information by using  NOT IN.As we have to fetch the cus_code  of  those customer  where customer code  does not exist in invoice table.As a parameter  to IN we have to pass the subquery that find the cus_code where invoice .cus_code=customer.cuscode

  1. Show the names of the customers for whom we DO NOT have invoices.

This query is same as query five the only difference is here we have to display the first name and last name of the customer.

  1. Find out how many times a customer generated an invoice – make sure the counted column heading reads “Invoices_generated”.

To retrieve this information we have to use inner join on two tables  customer and invoice.and to count the generated invoices the aggregate function count() is used. And to give the  column heading we can use alias by using ‘as’ keyword. the alias for new column  is “Invoices_generated”.

  1.  List the names of the customers and how many times a customer generated an invoice – make sure the counted column heading reads “N_Of_Invoices”.

This query is same as the previous one. The difference here is in this we have to project the columns first name and last name of customer table. The result of this query will display how may times a customer generated invoice  with name to the corresponding customer. Here the alias for new column  is “N_Of_Invoices”.

Part B – DDL

1.Add a new row/record to the `customer` table to include your actual student_ID in the CUS_CODE, your last_name as the CUS_LNAME` and yourfirstname as CUS_FNAME. Add any other details of your choice in all the other fields.

This query is used to add one reacord in the customer table where I have insert my student id as cus_code and my firstname as customer firstname  and my lastname as customer last name and all my details for other columns

  1. Add a new attribute (field) credit_history VARCHAR(6) to the customer table.

To implement this query we have to change the  structure  of the table  customer by using alter command.

In alter command we have to use add column  option with column name i.e credit_history  and its datatype i.e VARCHAR and the size i.e 6.

  1. Update credit_history for customer ‘10011’to ‘Bad’

For this we have to use the update  query to update one record in customer table.we have to set the value ‘Bad ’ where the cus_code is ‘10011’.

     4. Delete the employee Leighla Genkazi from the EMP table.

In this query we have to delete one record from emp table where the  emp_fname=’ Leighla’ and  emp_lname=’ Genkazi’

  1. Add a new attribute called ‘email_address’ to ‘emp’ table with data type and length ‘VARCHAR(20).

To alter the structure  of the table  emp  we have to use alter table query  with add column  option where the column name is ‘email_address’  and its datatype is  VARCHAR and the length is 20.

Search Here

Latest Reviews

  • Marcus
    30 Apr, 2019

    My assignment help services are provided in the best way by this website. The writers have completed my assignment in such a way that they helped me increase my overall performance.

  • Emy
    29 Apr, 2019

    Awesome work done by the experts of this company. I visited this site to get math assignment completed. My assignment was complete in every aspect. Thanks to the professionals who provided assignment writing help.

  • Darina
    25 Apr, 2019

    All thanks to the writers of Assignment Help 4 Me who completed my Physics assignment in the right manner. The structure of the assignment was totally customized according to my needs and that too at cheap prices.

View All Reviews

Facebook

Assignment Help 4 Me