Can someone give me the syntax/structure for these questions? I know you wont kn
ID: 3827467 • Letter: C
Question
Can someone give me the syntax/structure for these questions? I know you wont know what are the actual variables and names that need to be placed, but please do your best filling them in with dummy variables, its for SQL, im using my SQL workbench. Thanks in advance, also i know some of the questions are a bit unclear, sorry in advance too.
Using employee database answer the following:
1.Combine Individual and Business into one table that shows Customer Id, Name, and Begin Date where Name is First + Last for invididual accounts, and Begin Date is DOB for individual and incorporation for Business.
2.. For each product_cd construct the query that will return 1 row of result where the columns are the product names and the result is how many.
3.Determine if a customer is a business or individual customer and print out their name. You must do the detection using the fed_id column.
4.For each product type print out the average account balance.
*the Bolded italisized names are separate tables. the bolded rest are columns in those tables. any questions please feel free to ask. again, I dont expect perfect answers, I just need some guidance, thank you again
Explanation / Answer
As far as I understood your question here is my corresponding answers:
1. For your 1st question your wanted to join two tables i.e. Individual and Business so lets create both of them first
create table Individual (CustomerID integer , FirstName varchar(50), LastName varchar(50), DOB date)
create table Business (CustomerID integer , Incorporation varchar(20))
As join can happen only b/w the tables which has atlest one column in similar as per your reqirement it is
"CustomerID" which gonna same in both the tables
To concate two columns of a table and provide your own Alias we need to do something as follow:
select column1+column2 as MyColumn from Dummy
Insert some dummy values in both the tables
insert into Individual values (1, 'Jack', 'Jones', '1990-01-20')
insert into Individual values (2, 'Mike', 'Hussy', '1989-01-27')
insert into Individual values (3, 'Bill', 'Gates', '1990-07-21')
insert into Individual values (4, 'Chris', 'Martin', '1980-04-11')
insert into Business values (1, 'Mike')
insert into Business values (2, 'Jack')
insert into Business values (3, 'Chris')
insert into Business values (4, 'Bill')
and finally we need to join as per your requirement so final query for your first question will be:
Select I.CustomerID , I.FirstName+ ' '+I.LastName as Name, I.DOB as BeginDate, B.Incorporation from Individual as I inner join Business as B on B.CustomerID = I.CustomerID
Perhaps my reply is somewhere near to your question, please let me know if not.
For other 3 questions we need some clarity, please don't mind