Scribd
Upload
4 views2 pages

do3

The document contains SQL queries to create a database and two tables, cc_detail and cust_detail, for managing financial dashboard data. It includes commands to import data from CSV files into these tables and provides troubleshooting tips for common errors related to date formatting. Additionally, it outlines steps for copying additional data into the tables.

Uploaded by

virajsawant0293
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
4 views2 pages

do3

The document contains SQL queries to create a database and two tables, cc_detail and cust_detail, for managing financial dashboard data. It includes commands to import data from CSV files into these tables and provides troubleshooting tips for common errors related to date formatting. Additionally, it outlines steps for copying additional data into the tables.

Uploaded by

virajsawant0293
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

E:\proj_1\project\SQL Query - Financial Dashboard Data.

sql 1
1 -- SQL Query to create and import data from csv files:
2
3 -- 0. Create a database
4 CREATE DATABASE ccdb;
5
6 -- 1. Create cc_detail table
7
8 CREATE TABLE cc_detail (
9 Client_Num INT,
10 Card_Category VARCHAR(20),
11 Annual_Fees INT,
12 Activation_30_Days INT,
13 Customer_Acq_Cost INT,
14 Week_Start_Date DATE,
15 Week_Num VARCHAR(20),
16 Qtr VARCHAR(10),
17 current_year INT,
18 Credit_Limit DECIMAL(10,2),
19 Total_Revolving_Bal INT,
20 Total_Trans_Amt INT,
21 Total_Trans_Ct INT,
22 Avg_Utilization_Ratio DECIMAL(10,3),
23 Use_Chip VARCHAR(10),
24 Exp_Type VARCHAR(50),
25 Interest_Earned DECIMAL(10,3),
26 Delinquent_Acc VARCHAR(5)
27 );
28
29
30 -- 2. Create cc_detail table
31
32 CREATE TABLE cust_detail (
33 Client_Num INT,
34 Customer_Age INT,
35 Gender VARCHAR(5),
36 Dependent_Count INT,
37 Education_Level VARCHAR(50),
38 Marital_Status VARCHAR(20),
39 State_cd VARCHAR(50),
40 Zipcode VARCHAR(20),
41 Car_Owner VARCHAR(5),
42 House_Owner VARCHAR(5),
43 Personal_Loan VARCHAR(5),
44 Contact VARCHAR(50),
45 Customer_Job VARCHAR(50),
46 Income INT,
47 Cust_Satisfaction_Score INT
48 );
49
50
51 -- 3. Copy csv data into SQL (remember to update the file name and file
location in below query)
52
E:\proj_1\project\SQL Query - Financial Dashboard [Link] 2
53 -- copy cc_detail table
54
55 COPY cc_detail
56 FROM 'D:\credit_card.csv'
57 DELIMITER ','
58 CSV HEADER;
59
60
61 -- copy cust_detail table
62
63 COPY cust_detail
64 FROM 'D:\[Link]'
65 DELIMITER ','
66 CSV HEADER;
67
68
69
70 -- If you are getting below error, then use the below point:
71 -- ERROR: date/time field value out of range: "0"
72 -- HINT: Perhaps you need a different "datestyle" setting.
73
74 -- Check the Data in Your CSV File: Ensure date column values are
formatted correctly and are in a valid format that PostgreSQL can
recognize (e.g., YYYY-MM-DD). And correct any incorrect or missing
date values in the CSV file.
75 -- or
76 -- Update the Datestyle Setting: Set the datestyle explicitly for your
session using the following command:
77 SET datestyle TO 'ISO, DMY';
78
79 -- Now, try to COPY the csv files!
80
81
82 -- 4. Insert additional data into SQL, using same COPY function
83
84 -- copy additional data (week-53) in cc_detail table
85
86 COPY cc_detail
87 FROM 'D:\cc_add.csv'
88 DELIMITER ','
89 CSV HEADER;
90
91
92 -- copy additional data (week-53) in cust_detail table (remember to
update the file name and file location in below query)
93
94 COPY cust_detail
95 FROM 'D:\cust_add.csv'
96 DELIMITER ','
97 CSV HEADER;
98
99

You might also like