Scribd
Upload
13 views5 pages

Solved Exercises of SQL Server

This document presents several solved exercises in SQL Server that include: 1) queries to related tables, 2) table creation, 3) creation of stored procedures, and 4) creation of triggers. Examples of nested queries, record insertion, salary increments, database copying, user registration, and stock deductions are shown.

Uploaded by

ScribdTranslations
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
13 views5 pages

Solved Exercises of SQL Server

This document presents several solved exercises in SQL Server that include: 1) queries to related tables, 2) table creation, 3) creation of stored procedures, and 4) creation of triggers. Examples of nested queries, record insertion, salary increments, database copying, user registration, and stock deductions are shown.

Uploaded by

ScribdTranslations
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Solved Exercises (SQL SERVER)

PART 1: Inquiries
-------------------------------------------------------------------------
CREATE DATABASE BANK
GO
USEBANK
GO
CREATE TABLE CLIENTE
(IDCLIENTEVARCHAR(5)PRIMARY KEY,
LASTNAME VARCHAR(30)
NAMESVARCHAR(30))
GO
CREATE TABLE ACCOUNT
(CLIENTIDVARCHAR(5),
ACCOUNT_ID VARCHAR(5) PRIMARY KEY
BALANCEFLOAT
DIVISAVARCHAR(20))
GO
CREATE TABLE MOVIMIENTO
(ACCOUNTIDVARCHAR(5),
MOVEMENTID VARCHAR(5) PRIMARY KEY,
VARCHAR(2)
MONTOFLOAT,
CLOSEDATETIME
GO
ALTER TABLE ACCOUNT
ADD FOREIGN KEY(IDCLIENTE) REFERENCES CLIENTE
GO
ALTER TABLE MOVIMIENTO
ADD FOREIGN KEY(IDACCOUNT) REFERENCES ACCOUNT
GO

--According to the Model, Ask the Following Questions


--1A --Check the Accounts of clients whose Last Name starts with A

SELECT [Link] + ',' + [Link] AS CLIENT, [Link], [Link]


FROM ACCOUNT A INNER JOIN CLIENT B
[Link]=[Link]
WHERE [Link] LIKE 'A%'

--1B–Consult all the Movements of the Accounts in EURO currency of


Client whose last name is PEREZ

SELECT A.ACCOUNT_ID, [Link], [Link], TYPE = CASE WHEN 'D' THEN 'DE'
POSITION 'WHEN' 'THEN' WITHDRAWAL 'WHEN' 'THEN' TRANSFER 'END
FROM ACCOUNT A INNER JOIN CLIENT B ON [Link] = [Link]
INNER JOIN MOVIMIENTO ON [Link] = [Link]
WHERE [Link]='EUROS' AND [Link]='PEREZ'
ORDER BY [Link]

--1C--Consult all account movements of the CLIENT PEREZ in


EUROS (Even if you have accounts in other currencies) Ordered by the number of
Count

SELECT C.FIRST_NAME, C.LAST_NAME, CU.ACCOUNT_ID, [Link], [Link], M.MOVEMENT_ID


LIAR
FROM CLIENT INNER JOIN ACCOUNT CU
[Link]=[Link]
INNER JOIN MOVIMIENTO M
[Link] = [Link]
WHERE [Link]='PEREZ'
ORDER BY [Link]

Consult the Currencies that do not have any Associated Account

SELECT [Link], [Link], [Link], [Link], [Link]


FROM ACCOUNT A INNER JOIN CLIENT B ON [Link] = [Link]
INNER JOIN MOVIMIENTO ON [Link] = [Link]
WHERE [Link]='WITHOUT ACCOUNT'

Consult the Income Movements of PEREZ and the Movements of


Transfer of RODRIGUEZ

[Link]
FROM ACCOUNT A INNER JOIN CLIENT B ON [Link] = [Link]
INNER JOIN MOVIMIENTO ON [Link] = [Link]
WHERE [Link]='DEPOSIT' AND B.LAST_NAME='PEREZ' OR
[Link]='TRANSACTIONS' AND B.LAST_NAMES='RODRIGUEZ'
ORDER BY [Link]

CREATE A FUNCTION THAT RETURNS IF A CLIENT IS DELINQUENT

CREATE FUNCTION MOROSO(@FECHAPAGO DATETIME)


RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @FECHACLIENTEDATETIME
DECLARE @MESSAGE VARCHAR(20)
SELECT @CLIENTDATE = DATEFROMMOVEMENT WHERE TYPE = 'DEPOSIT'
IFMONTH(@CLIENTDATE) < MONTH(@PAYDATE)
THE CUSTOMER IS DELINQUENT
RETURN@MESSAGE
END
--DEMO
SELECT [Link]('02/15/2011')
PART 2: Creation of Objects - Tables
-------------------------------------------------------------------------
--
2A--CREATION OF TABLES
GO
CREATE DATABASE COMPANY
GO
USECOMPANY
GO
CREATE TABLE EMPLOYEES
(ID INT NOT NULL IDENTITY,
DOCUMENTOVARCHAR(30) NOT NULL,
LASTNAME VARCHAR(30) NOT NULL,
NAME VARCHAR(30) NOT NULL
SECTIONVARCHAR(20) NOT NULL,
FLOATSALARYNULL
ENTRY DATE TIME NULL
GO
CREATE TABLE CONTROLS
(USUARIOVARCHAR(5),
CLOSEDATETIME
GO
-------------------------------------------------------------------------
CREATE TABLE COPIAEMPLEADOS
(IDINTNOT NULL,
DOCUMENTOVARCHAR(30) NOT NULL,
LASTNAME VARCHAR(30) NOT NULL
NAME VARCHAR(30) NOT NULL
SECTION VARCHAR(20) NOT NULL
SALARYFLOATNULL
DATE ENTRY DATETIME NULL
GO
CREATE TRIGGER TRG_COPIAEMPLEADOS
ONEMPLOYEES
FOR INSERT
AS
BEGIN
INSERT INTOCOPIAEMPLEADOS
SELECT * FROM INSERTED
END
GO
--2B—ENTER RECORDS
INSERT
INTOEMPLOYEESVALUES('22999000','CORDOVA','WLIMER','SYSTEMS',680,'24/1
0/2005
INSERT
UNEMPLOYED VALUES ('22333444', 'ACOSTA', 'ANA', 'SECRETARY', 500, '05/05/
2000')
INSERT
INTOEmployees VALUES('22777888','CORDOVA','DANIELA','SECRETARY',560,'2
5/08/2006
GO
--DEMO
SELECT * FROM EMPLOYEES
SELECT * FROM EMPLOYEES COPY
--PART 3 -- Creation of Stored Procedures
Increase Salary of Employees with more than 3 Years of Service to
20% more
GO
CREATE PROCEDURE USP_EMPLOYEES_INCREASESALARY
AS
DECLARE @FECHADATETIME
DECLARE @DIFTINYINT
SELECT @DATE=ENTRYDATEFROMEMPLOYEES
SET @DIF = YEAR(GETDATE()) - YEAR(@DATE)
IFMONTH(GETDATE()) < MONTH(@DATE)
SET @DIF = @DIF - 1
IFMONTH(GETDATE()) = MONTH(@DATE) AND
DAY(GETDATE()) < DAY(@DATE)
SET @DIF = @DIF - 1
IF(@DIF >= 3)
UPDATE EMPLOYEES SET SALARY = (SALARY + (SALARY * 0.20))
GO
--EXECUTE ONLY ONCE
EXECUTE USP_EMPLOYEES_INCREASESALARY
GO
--CHECKING
SELECT * FROM EMPLOYEES
-------------------------------------------------------------------------
Procedure that Allows Storing Null Values in the Fields
Salary
GO
CREATE PROCEDURE USP_EMPLOYEEINSERT
@DOCUMENTOVARCHAR(30)
@LASTNAMEVARCHAR(30)
@NAMEVARCHAR(30)
@SECTIONVARCHAR(20)
AS
BEGIN
INSERT
INSERT INTO EMPLOYEES VALUES (@DOCUMENT, @LASTNAME, @FIRSTNAME, @SECTION, NULL, NULL)
END
GO
--DEMO
EXECUTE USP_EMPLEADOS_INGRESAR
GO
VERIFYING
SELECT * FROM EMPLOYEES
----------------------------------------------------------------------
--3C—Procedure to Perform BACKUP of the COMPANY Database
GO
CREATE PROCEDURE COPY DATABASE
As
BACKUP DATABASE COMPANY TO DISK='F:\[Link]'
WITH INIT
GO
--DEMO
EXECUTECOPIADATABASE
GO
--PART 4—CREATION OF TRIGGERS
-------------------------------------------------------------------------
--4A -- TRIGGER THAT RECORDS THE USER AND THE DATE ON WHICH IT
MAKE AN INSERT INTO THE EMPLOYEES TABLE AND STORE THEM IN THE TABLE
CONTROLS
GO
CREATE TRIGGER TRG_CONTROLES
ONEMPLOYEES
FOR INSERT
AS
INSERT INTO CONTROLES VALUES (Suser_Name(), GETDATE())
GO
--DEMO
INSERT
UNEMPLOYED VALUES('22777899','GONZALES','LUIS','SECRETARY',560,'25/
08/2008
GO
SELECT * FROM CONTROLES
-------------------------------------------------------------------------
--4B–TRIGGER THAT DISCOUNTS THE EXISTENCE OF THE PRODUCTS TABLE
ACCORDING TO THE ORDER
GO
CREATE DATABASE WAREHOUSE
GO
USEALMACEN
GO
CREATE TABLE PRODUCTS
(ID_PRODUCTO CHAR(8) PRIMARY KEY NOT NULL,
PRODUCTNAME VARCHAR(25) NOT NULL,
EXISTENCEINTNULL
PRECISODECIMAL(10,2) NOT NULL,
DECIMAL(10,2) SELLING PRICE
GO
CREATE TABLE ORDER
ORDER_ID IDENTITY,
PRODUCT_ID CHAR(8) NOT NULL,
ORDER_QUANTITY
CONSTRAINT PK_ID_PRODUCT FOREIGN KEY (ID_PRODUCT) REFERENCES PRODUCTS (ID
PRODUCT
GO
--TRIGGER
CREATE TRIGGER TRG_ORDER_ITEMS
ONREQUEST
FOR INSERT
AS
UPDATE PRODUCTS SET STOCK = STOCK -
(SELECT QUANTITY_ORDERED FROM INSERTED)
WHERE ID_PRODUCT = (SELECT ID_PRODUCT FROM INSERTED)
GO
INSERT INTO PRODUCTS VALUES('P001','SCREEN FILTERS',5,10,12.5)
INSERT INTO PRODUCTS VALUES('P002','SPEAKERS',7,10,11.5)
INSERT INTO PRODUCTS VALUES('P003','MOUSE',8,4.5,6)
GO
--DEMO
INSERT INTO PEDIDO VALUES('P003', 5)
VERIFYING
SELECT * FROM PRODUCTS
SELECT * FROM ORDER

You might also like