May 30, 2021 at 10:21 am
dear experts,
I need to to design “Customer Management” with the following modules:
1. Customer registration
a. Alphanumeric including multi Asian language for first name and last name, each have a maximum 30 characters
b. Username (for login in website) must consist:
i. Alphanumeric including multi Asian language
ii. between 8 and 20 characters
c. Customer ID must consist:
i. 30 characters
ii. First 5 is ALPHA character
iii. Last 25 is NUMERIC character
2. Customer Activities
a. Login
b. Logout
c. Change Password
3. Customer Reports
a. List of new customers, with the following filters
i. Range of date and time, order descending
ii. Customer ID (optional)
iii. Paging
b. List of customer’s login sessions, with the following filters
i. Range of date and time
ii. Username (optional)
iii. Paging
how to :
1. Design tables:
a. Create all necessary tables and provide the examples data
2. Stored procedures:
a. Create all necessary stored procedures and provide the example how to use it
based on the example data of 1.a.
thank you
May 30, 2021 at 4:13 pm
Well, I have made this for a start:
please correct me if im Wrong
note: all of this data is just example and not Real
Answers:
--1.A CREATE DATABASE FIRST
USE [master]
GO
CREATE DATABASE [CUSTOMER MANAGEMENT]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'CUSTOMER MANAGEMENT', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\CUSTOMER MANAGEMENT.mdf' ,
SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'CUSTOMER MANAGEMENT_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\CUSTOMER MANAGEMENT_log.ldf' ,
SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
--CREATE Table:
--Create table Customers:
CREATE TABLE dbo.customers
( ID int IDENTITY(1,1) NOT NULL,
PerFix varchar(30) NOT NULL,
Customer_ID AS(PerFix+RIGHT('0000000'+CAST(id AS VARCHAR(25)),30)) PERSISTED,
username AS(PerFix+RIGHT('000000000000000'+CAST(id AS VARCHAR(25)),30)) PERSISTED not null,
First_name CHAR(30) NOT NULL,
last_name char(30) NOT NULL,
region CHAR (50) NOT NULL,
created_date datetime NOT NULL
);
--insert data into dbo.customers
insert into dbo.customers
(PerFix,First_name,last_name,region,created_date)
--VALUES ('Joker','Brody','Lee','HONGKONG', getdate())
--VALUES ('Realm','Adam','Brook','THAILAND', '2021-05-29 09:00:01.150')
VALUES ('JOKER','Alan','Budikusuma','INDONESIA', '2021-05-29 09:03:10.100')
--drop table dbo.customers
SELECT * FROM CUSTOMERS;
--B. Customers Activities
--create Table LOGIN
create table dbo.login
(Username char(30) NOT NULL,
[Password] nchar(30) NOT NULL,
login_time datetime not null,
logout_time datetime not null
);
insert into dbo.login
--values('Joker0000000000000001', 'jokergood','2021-05-29 12:00:01.150','2021-05-29 12:30:45.000')
values('Realm0000000000000002', 'champione','2021-05-29 23:00:00','2021-05-30 01:00:00')
select * from login;
--2. Create Stored Procedure:
---SP AUTHENTICATED USER:
CREATE PROCEDURE AuthenticateUser
@UserName varchar(30),
@Password varchar(30),
@Role varchar(25) OUTPUT
AS
If ((SELECT Count (*) From dbo.[login] Where UserName COLLATE SQL_Latin1_General_CP1_CS_AS = @Username
And Password COLLATE SQL_Latin1_General_CP1_CS_AS = @Password) = 0)
Begin
If ((SELECT Count (*) From dbo.[login] Where UserName COLLATE SQL_Latin1_General_CP1_CS_AS = @Username) = 0)
Begin
Select @Role = 'Incorrect User Name'
End
Else
Begin
Select @Role = 'Incorrect Password'
End
End
Else
Begin
Select @Role = 'Logged in Successfully'
End
GO
Declare @Role VarChar (100)
Exec AuthenticateUser 'Joker0000000000000001', 'jokergood', @Role Output
Print @Role
Exec AuthenticateUser 'Realm0000000000000002', 'wrong', @Role Output
Print @Role
Exec AuthenticateUser 'Realm0000000000000004', 'unforgotten', @Role Output
Print @Role
SELECT customer_id, LoginTime=MIN(login_time), LogoutTime=MAX(logout_time),SUM(ISNULL(
DATEDIFF(minute, login_time, logout_time),
-1000000)) AS Duration
FROM
(
SELECT customer_id, login_time,logout_time
,rn=(ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY logout_time)-1)/2
FROM customers a
inner join [login] b on a.username=b.username
GROUP BY Customer_ID,login_time,logout_time) x
-- When we group by rn we have paired sessions
GROUP BY Customer_ID, rn
ORDER BY Customer_ID;
May 30, 2021 at 5:51 pm
That's too much for a single q here. You need to break this into separate qs, each with a single and specific q.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply