Remember --- need both the SQL and the output to get any marks. Question 2 Reminder === > Issue SET PAGESIZE 200 at beginning of your session. Display the (1) customer number, (2) customer name and (3) country code for all the customers that are in the Germany. Look up th country code for Germany. Table used is CUSTOMERS Please note that the user is to enter the 2 character country code AND that they if they were doing Canade they can enter it as CA, Ca, ca, cA meaning any combitaion. Your SQL must allow for various user inputs of the code. Do not make the user enter it the way you want such as CA only. Be flexible and helpful. Also do not use a lot of OR statements to get around the requirements. NOTE: the user is not entering the country name.
-
Remember --- need both the SQL and the output to get any marks.
Question 2
Reminder === > Issue SET PAGESIZE 200 at beginning of your session.
Display the (1) customer number, (2) customer name and (3) country code for all the customers that are in the Germany. Look up th country code for Germany. Table used is CUSTOMERS
Please note that the user is to enter the 2 character country code AND that they if they were doing Canade they can enter it as CA, Ca, ca, cA meaning any combitaion.
Your SQL must allow for various user inputs of the code. Do not make the user enter it the way you want such as CA only. Be flexible and helpful. Also do not use a lot of OR statements to get around the requirements.
NOTE: the user is not entering the country name.
Table Creation and Insertion:
CREATE TABLE COUNTRY(
countryCode VARCHAR(2) PRIMARY KEY,
countryName VARCHAR(50)
)
INSERT INTO COUNTRY VALUES('DE','Germany');
INSERT INTO COUNTRY VALUES('UK','United Kingdom');
INSERT INTO COUNTRY VALUES('CA','Canada');
CREATE TABLE CUSTOMERS(
customerNumber INT PRIMARY KEY,
customerName VARCHAR(50),
City VARCHAR(50) ,
countryCode VARCHAR(2),
FOREIGN KEY(countryCode) REFERENCES COUNTRY(countryCode)
)
INSERT INTO CUSTOMERS VALUES(100,'John','Georgia','UK')
INSERT INTO CUSTOMERS VALUES(200,'Mallick','Downtown','DE')
INSERT INTO CUSTOMERS VALUES(300,'Inba','Ohio','DE')
INSERT INTO CUSTOMERS VALUES(400,'Albery','Kallu','CA')
INSERT INTO CUSTOMERS VALUES(500,'Lally','Duluth','dE')
Question 2
Display the (1) customer number, (2) customer name and (3) country code for all the customers that are in the Germany. Look up th country code for Germany. Table used is CUSTOMERS
Query:
- Since SQL is case insensitive, no need to specify countryCode as DE,de,De,dE separately
!-- Query For Question 2
SELECT customerNumber, customerName , countryCode FROM CUSTOMERS WHERE countryCode in ('DE')
Sample Run:
**************************************************************************************
Feel free to rate the answer and comment your questions, if you have any.
Step by step
Solved in 2 steps with 1 images
