Sql Assignment 2

50 %
50 %
Information about Sql Assignment 2

Published on March 15, 2016

Author: AnjuRenjith

Source: slideshare.net

1. Assignment2 • Create trigger forthe table EmployeeAllowance forinsert,delete andupdate Create a table say logtable ( intID- auto incrementing PrimaryKey EmployeeID, AllowanceID, Amount, Operation - vrachra(15), ChangedDate ) Inserttriggershouldinsertanentryforthe newlyinsertedrecord Update triggershouldinsertanentryfor the record before update Delete triggershouldinsertforthe recordwhichisgoingto be deleted The logtable shouldhave atextfield(operation) whichhasthe values - insert/update/delete. The ChangedDate shouldhave the date atthe time of insert/update/delete. • CREATE TABLE #temp ( name NVARCHAR(50) NULL, middlenameNVARCHAR(50) NULL, lastname NVARCHAR(50) NULL ); INSERT INTO#tempVALUES( 'Name',NULL, 'Last Name'); INSERT INTO#tempVALUES( 'Name','Middle Name','Last Name'); SELECT name +middlename+lastname ASResult FROM#temp; Withoutusing ISNULLhow can youreturnthe same outputforboth the selectstatements? • Queryto getthe stringafterthe secondhypheninanystring. Suppose the stringisSQL-Server-2012-Training. Output must be 2012-Training • Queryto insert2012 in the string‘SQL ServerTraining’. Output must be SQL Server 2012 Training • Suppose Ihave a table witha bitcolumn‘Gender’withvaluesTrue andFalse.Ineedtomake the True valuestoFalse andFalse valuestoTrue.Write a Queryfor it. CREATE TABLE [dbo].[StudentMarks] ( [Id] intNULL, [Name] varchar(20) NULL, [Marks] floatnull ) INSERT INTO[StudentMarks] VALUES (1, 'Abin', 100), (2, 'Prasad',90), (3, 'Arjun',80), (4, 'Anish',82), (5, 'Reshma',70),

2. (6, 'Ajay',100), (7, 'Jisna',83), (8, 'Jins',90), (9, 'Reshmi',65), (10, 'Ajayan',23), (11, 'Antony',45), (12, 'Bhaskar',70), (13, 'Lekshmi',55), (14, 'Ajaya ',90), (15, 'Anto',100), (16, 'Kalyani',65) I needtoget the Rank of students. Note:Studentswith the same mark must have the same rank • CREATE TABLE [dbo].[SplitColumns] ( [Id] [int] NULL, [Name] [varchar](20) NULL ) INSERT INTO[SplitColumns] VALUES (1, 'Abin,Ashok'), (2, 'Prasad,G'), (3, 'Arjun,Ramachandran'), (4, 'Anish,Kumar'), (5, 'Reshma,Rajan'), (6, 'Ajay,Krishnan'), (7, 'Jisna,Antony'), (8, 'Jins,Peter') GO The outputmust be as below: Write 3 queriesusingthe three followingmethods • Substringandcharindex • XML • Parsename • Is a primarykeya clusteredindex?Why? • CREATE TABLE [dbo].[Team]( [MemberId] [int] , [Name] [varchar](50) , [LeadId] [int] ,

3. ) INSERT INTOdbo.Team ([MemberId],[Name],[LeadId]) VALUES (1,'Mathew',NULL), (2,'John',NULL), (3,'Manmohan',NULL), (4,'Kannan',1), (5,'Jibu',2), (6,'Kiran',3), (7,'Sandeep',2), (8,'Joe',4), (9,'Prasad',3), (10,'Abin',3), (11,'Minu',1), (12,'Rekha',2), (13,'Ismail',5) Outputisrequiredinthe followingformat: Write 2 queriesone withSelfJoin and the other withCTE. • DECLARE @ProjectPlanTABLE ( ProjectCode VARCHAR(10), PlanCode VARCHAR(10), StartDate DATE ) INSERT INTO@ProjectPlanVALUES('Proj00001', 'P00001', '1-Sep-2014') INSERT INTO@ProjectPlanVALUES('Proj00001', 'P00002', '1-Oct-2014') INSERT INTO@ProjectPlanVALUES('Proj00001', 'P00003', '10-Oct-2014') INSERT INTO@ProjectPlanVALUES('Proj00001', 'P00004', '25-Oct-2014')

4. INSERT INTO@ProjectPlanVALUES('Proj00002', 'P00001', '1-Oct-2014') INSERT INTO@ProjectPlanVALUES('Proj00002', 'P00002', '1-Nov-2014') Outputmustbe as below For Proj00001, the PlanP00001 starts on 1-Sep-2014 and as PlanP00002 starts at 1-Oct-2014, the planP00001 endson 30-Sept-2014. • CREATE TABLE dbo.Location( LocationIDintNOT NULL, LocationName varchar(100) NOT NULL, ParentLocationIDintNULL, LocationType varchar(20) NOT NULL CONSTRAINTPK_LocationPRIMARYKEY CLUSTERED ( LocationIDASC ) ON [PRIMARY]) INSERT INTOdbo.Location(LocationID,LocationName,ParentLocationID,LocationType) VALUES(1,'India',null,'Country') INSERT INTOdbo.Location(LocationID,LocationName,ParentLocationID,LocationType) VALUES(2,'UnitedStates',null,'Country') INSERT INTOdbo.Location(LocationID,LocationName,ParentLocationID,LocationType) VALUES(3,'Kerala',1, 'State') INSERT INTOdbo.Location(LocationID,LocationName,ParentLocationID,LocationType) VALUES(4,'Kollam',3, 'City') INSERT INTOdbo.Location(LocationID,LocationName,ParentLocationID,LocationType) VALUES(5,'Kochi',3, 'City') INSERT INTOdbo.Location(LocationID,LocationName,ParentLocationID,LocationType) VALUES(6,'Kozhikode',3,'City') INSERT INTOdbo.Location(LocationID,LocationName,ParentLocationID,LocationType) VALUES(7,'Florida',2, 'State') INSERT INTOdbo.Location(LocationID,LocationName,ParentLocationID,LocationType) VALUES(8,'Miami', 7, 'City')

5. INSERT INTOdbo.Location(LocationID,LocationName,ParentLocationID,LocationType) VALUES(9, 'Illinois',2,'State') INSERT INTOdbo.Location(LocationID,LocationName,ParentLocationID,LocationType) VALUES(10, 'Chicago',9, 'City') INSERT INTOdbo.Location(LocationID,LocationName,ParentLocationID,LocationType) VALUES(11, 'Chennai',1,'State') INSERT INTOdbo.Location(LocationID,LocationName,ParentLocationID,LocationType) VALUES(12, 'Nagercoil',11,'City') The Country name will be givenas input. Write a query to get the citiesin the country. • Write scriptto add Checkconstraintsinthe EmployeeAllowance table whichwill make sure the Amountisnot null andalwaysgreaterthanzero. • Write scriptto add Unique constraintforthe columnAllowanceName inthe Allowance table • Write a queryto findthe nth highestsalaryfromthe EmployeeAllowancetable.Choosethe best method.Why? • CREATE TABLE [dbo].[Details] ( [Id] intNULL, [Name] nvarchar(50) NULL, [Address] nvarchar(200) NULL, [DateofEntry] datetime NULL ) INSERT INTO[Details] VALUES (1, 'Abin,Ashok','Address1Address1Address1','2015-10-25'), (2, 'Prasad,G','Address1Address1','2000-11-01'), (3, 'Arjun,Ramachandran','Address1','1998-05-10'), (4, 'Anish,Kumar','Address1Address1Address1','1999-01-01'), (5, 'Reshma,Rajan','Address1Address1','1999-12-12'),

6. (6, 'Ajay,Krishnan','Address1Address1','2013-06-01'), (7, 'Jisna,Antony','Address1vAddress1','2011-07-01'), (8, 'Jins,Peter','Address1Address1','1999-08-21') , (9, 'Abin,Ashok','Address1Address','1996-09-22'), (10, 'Abin,Ashok','Address1Address','1980-02-23'), (11, 'Abin,Ashok','Address1AddressAddress','1970-03-24'), (12, 'Arjun,Ramachandran','Address1AddressAddress','1960-04-25'), (13, 'Anish,Kumar','Address1000','1950-02-26'), (14, 'Reshma,Rajan','Address1Address1000','1962-04-27'), (15, 'Ajay,Krishnan','Address1Address1000','1945-08-28'), (16, 'Arjun,Ramachandran','Address1Address1000','1985-10-29'), (17, 'Anish,Kumar','Address1Address1000Address1000','1942-12-30'), (18, 'Reshma,Rajan','Address1Address1000Address1000Address1000','1974-12-31'), (19, 'Ajay,Krishnan','Address1Address1000','1975-11-01'), (20, 'Arjun,Ramachandran','Address1Address1000','2015-12-02'), (21, 'Anish,Kumar','Address2000Address2000','2009-01-03'), (22, 'Reshma,Rajan','Address1Address2000','2008-04-04'), (23, 'Ajay,Krishnan','Address1Address2000','2004-09-05'), (24, 'Prasad,G', 'Address1Address2000Address2000','2013-02-13'), (25, 'Prasad,G', 'Address1Address2000Address2000','1993-11-20') Write a queryto findthe latestaddressof eachperson. Write anotherqueryto findthe numberof duplicate recordsof eachperson • Inputisa stringand a character( can be a single quotes,analphabet,anumber,acomma , any special character) withinthe string.

7. Write a queryto delete the charactersinthe stringafterthe inputcharacter. Eg: the stringis‘SQLServer-2000’and the character is -. The outputwill be ‘SQLServer’

#temp presentations

Add a comment

Related pages

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

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

Assignment description - Homework Tutoring - Homework Help ...

In this assignment you will build a sample database similar to the preferred solution for Assignment 2. ... following the examples given in the SQL Book.
Read more

Assignment Statement - PL/SQL Language Elements

Assignment Statement. ... PL/SQL allows aggregate assignment between entire records if ... emp_rec.department_id := deptid; emp_rec.job_id := jobids(2); ...
Read more

Assignment 2 - SQL-final - Scribd

Assignment 2 - SQL-final - Free download as Word Doc (.doc / .docx), PDF File (.pdf), Text File (.txt) or read online for free. SQL assignment
Read more

Northwind database exercise for Assignment 2 - YouTube

Northwind database exercise for Assignment 2 minderchen. Subscribe Subscribed Unsubscribe 2,256 2K. ... SQL Server 2008: 04 Sample Databases ...
Read more

SQL Assignment 2 – further guidelines - California State ...

SQL Assignment 2 – further guidelines Author: Cal State L.A. Last modified by: Cal State L.A. Created Date: 10/14/2009 5:43:00 PM Company: Cal State L.A.
Read more

Comp115 Assignment 2: Basic SQL Queries - Tufts CS

Comp115 Assignment 2: Basic SQL Queries. Please download this file and open it in a current version of Adobe Acrobat Reader. This will make the form items ...
Read more

Assignment description - Homework Tutoring - Homework Help ...

Language: Access SQL. Level: Basic. Price: 80$ Programming Assignment. You are given the following . SLB database, which contains three tables – STUDENT ...
Read more

CS2550 SQL Assignment #2

Perform SQL SELECT statements using Oracle Functions Description. Write a series of Oracle SQL query statements using the Student database which consists ...
Read more