Sql Assignment 3

50 %
50 %
Information about Sql Assignment 3

Published on March 15, 2016

Author: AnjuRenjith

Source: slideshare.net

1. 1. CREATE TABLE CountNULL ( ID INT NULL ) GO INSERT INTOCountNULL(ID) VALUES (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (2), (3), (8), (9), (NULL) SELECT COUNT(*) NULLCountFROMCountNULL WHERE ID ISNULL SELECT COUNT(0) NULLCount FROMCountNULL WHERE ID IS NULL SELECT COUNT(1) NULLCountFROMCountNULL WHERE ID IS NULL SELECT COUNT(5) NULLCountFROMCountNULL WHERE ID IS NULL Checkthe output.Explain 2. IF (OBJECT_ID('[dbo].[Student]','U') IS NOT NULL) BEGIN DROP TABLE [dbo].[Student] END GO CREATE TABLE [dbo].[Student] ( [Gender] bitNULL, [Name] varchar(20) NULL, [ID] intNULL ) GO

2. INSERT INTO[Student] VALUES (1, 'abc1',1), (0, 'abc1',2), (1, 'abc1',3), (1, 'def1',4), (1, 'def1',5), (1, 'ghi',6), (0, 'ghi',7), (1, 'jkl',8), (1, 'jkl',9), (1, 'jkl ',10), (1, 'mno',11), (1, 'mno',12), (1, 'mno',13), (1, 'mno ',14), (1, 'pqr',15), (1, 'stu',16), (1, 'stu',17) GO Write a queryto do the following Delete the firstrowamongall setsof duplicate rowsinthe table. If a recordhas no duplicates,deleteit. 3. Suppose Ihave a Shop table andan Itemtable.Write a querytoget the salesof each Shop and eachItem.The queryshould alsoreturnthe detailsof Shopswhichhadno salesforany item.The table structuresare given CREATE TABLE [dbo].[Shop] ( [ShopName] varchar(20) NULL, [ShopID] intNULL ) GO CREATE TABLE [dbo].[Item] ( [ItemName]varchar(20) NULL, [ItemID] intNULL, [ShopID] intNULL ) GO CREATE TABLE [dbo].[Sales] (

3. [ID] intNULL, [ItemID] intNULL, [ShopID] intNULL, [Amount] moneyNULL ) GO 4. Write a queryto add a columnintothe existingtable EmployeecalledAge.Itmustbe a computedcolumn.Wouldyoumake ita persistedor non-persistedcomputedcolumn? Why? 5. Suppose youneedtoadda columnEmail in a table withmillionsof records. Write queryfor it.Keepinmindthe situationwhere youwouldneedtosearch/update email idsof some/all recordsin the table. 6. You have createdforeignkeyconstraints.What are DELETE CASCADEandUPDATE CASCADE?Benefitsof usingthemif any. 7. Whichnormal form doyou generallyuse?Why?Demeritsof othernormal forms? 8. Create a DATA DICTIONARYforthe EmployeeDB.Whatisitsuse? 9. Suppose atable has columnswithnames –ID, Name,Address.Ineedtoadd a column – ‘Date of Birth’to the table insuch a way thatthe columnorderisID, FirstName,Date of Birth,Address.Howcan youdo that? 10. Findthe outputof the query DECLARE @value bit IF @value = 1 DECLARE @Sample TABLE (idint,textdatavarchar(50) ) ELSE INSERT INTO@Sample (id,textdata) select1, 'sample data' SELECT * FROM@Sample Explainthe output. 11. Example toillustrate the difference betweenUNION andUNION ALL. 12. Whenwill youuse EXCEPTand INTERSECT? For the scripts,sendme as .sql files. For the answers,sendme as.doc files.

Add a comment

Related pages

Assignment description - Homework Tutoring - Homework Help ...

Language: Oracle SQL. Level: Advanced. Price: 100$ Programming Assignment. Assignment 3 - Oracle Database Creation Overview. In this assignment you will ...
Read more

Database Systems Assignment 3: SQL Queries Questions

Database Systems Instructors: Hao-Hua Chu Winston Hsu Fall Semester, 2007 Assignment 3: SQL Queries Questions 1. The following relations keep track of ...
Read more

SQL Assignment 3 - Baylor University || School of ...

SQL Assignment 3 Due: 9:05 AM Sept. 11, 2014. Answer each query using 1 SQL statement. All answers must follow the documentation standard. Your assignment ...
Read more

SQL ASSIGNMENT(QUERIES) - Scribd - Read books, audiobooks ...

SQL ASSIGNMENT(QUERIES) - Free download as Word Doc (.doc), PDF File (.pdf), ... SQL Assignment 3. SQL Assignment 3. by vandana_korde. SQL LAB Assignment.pdf.
Read more

SQL Assignment - University of California, Berkeley

SQL Assignment Homework #3 CS 186, Fall 2006 Due: November 9th, 11:59 PM In this assignment, you’ll have to come up with SQL queries for the following ...
Read more

SQL lab 1 Create tables, insert data, and query

IS 4420 - SQL Lab 4 – Assignment 5 Solution . Total 100 points. Part A. Standalone Queries. ... 3 Headquarter 1 1 Manufacture 1 4 Finance ...
Read more

SQL Assignment 3 Further guidelines - California State ...

Assignment 3 (30 Pts.): Using SQL Oracle, design and prepare a trial balance based on the chart of accounts you prepared in assignment 2. Output 3: A ...
Read more

Assignment -3 on TSQL - SQL Server

SQL Server Training ... 3. Write a SELECT statement that returns one column from the Vendors table named Full Name.
Read more

SQL Key Assignment - Emily's Portfolio

SQL Key Assignment 0 SQL Key Assignment CS362-1202A-03 : Structured Query Language for Data Management Emily Hegge 4/16/2012 This is the Key Assignment for ...
Read more

Assignment-3-SQL-1 - Assignment: SQL #1 Getting Data out ...

View Homework Help - Assignment-3-SQL-1 from MIS 2502 at Temple. Assignment: SQL #1 Getting Data out of the Database For this assignment, you will be
Read more