Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

show the query for the following QUESTIONS CITY(CITY_CODE SMALLINT, CITY_NAME NVARCHAR(30)) CUSTOMER(C_CODE NVARCHAR(20), C_NAME NVARCHAR(30), C_LASTNAME NVACHAR(30), BIRTH_DATE DATE, PHONE_NUMBER CHAR(11), GROUP_CODE NVARCHAR(20), CITY_CODE SMALLINT)

show the query for the following

image text in transcribed

QUESTIONS CITY(CITY_CODE SMALLINT, CITY_NAME NVARCHAR(30)) CUSTOMER(C_CODE NVARCHAR(20), C_NAME NVARCHAR(30), C_LASTNAME NVACHAR(30), BIRTH_DATE DATE, PHONE_NUMBER CHAR(11), GROUP_CODE NVARCHAR(20), CITY_CODE SMALLINT) STOCK(STOCK_CODE NVARCHAR(20), STOCK_NAME NVARCHAR(50), PRICE NUMERIC(10,2), STOCK_GROUP_CODE NVARCHAR(20)) CUSTOMER_ACCOUNT(ID BIGINT, C_CODE NVARCHAR(20), OPERATION_DATE DATE, STOCK_CODE NVARCHAR(20), QUANTITY NUMERIC (5), PRICE NUMERIC(10,2), DEBT NUMERIC (10,2), RECEIVABLE NUMERIC(10,2), COMMENT NVARCHAR(50)) According to the tables above (CUSTOMER database), answer the following questions by using MS SQL Server. 1. "Database normalization is the process of organizing the columns (attributes) and tables (relations) of a relational database to reduce data redundancy and improve data integrity". Discuss how normalization is achieved on CUSTOMER database above (10 points) 2. Write an SQL statement to list all customers who were born in February and who are from SVAS. (C_CODE, C_NAME, C_LASTNAME will be listed) (10 points) 3. Write an SOL statement to list the stocks which have been sold so far at least 100 units (STOCK_CODE, STOCK_NAME, SoldQuantity) (10 points) 4. Write a stored procedure to decrease the prices (PRICE) at the given percentage in CUSTOMER_ACCOUNT table for customers with given GROUP CODE and CITY CODE (15 points) 5. Write a stored function to find how much a given stock group (STOCK_GROUP_CODE) was sold between two dates given in terms of money (15 points) 6. Write a t-sql code to create a trigger on any table above (You can create extra tables if you require) (10points) 7. Write the SOL statements to create a view and an index on any table above (10 points) 8. Write a stored procedure to send "Happy Birthday SMS message to customers who was born on given date by using a CURSOR variable (Assume that you already have SMS_SEND(PHONE_NUMBER, MESSAGE) stored procedure) (ex: Happy Birthday, Peri Gunes) (20 points)

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Database Marketing The Ultimate Marketing Tool

Authors: Edward L. Nash

1st Edition

0070460639, 978-0070460638

More Books

Students also viewed these Databases questions