Create a query using the tblCustomer table. Display CustomerID, FirstName, LastName, and a new calculated field named Coupon?, in that order. In the Coupon? field, use the IIf and Like functions to display No Coupon if the customer did not take the tour and Send 10% off next visit if the customer did take the tour.
Need help with the Iif and Like function above!!
Since you have not provided the db schema, I am providing the query by assuming the table and column names. Feel free to reach out in case of any clarifications.
QUERY
SELECT CustomerID, FirstName, LastName, IIF(Tour LIKE '%taken%', 'Send 10% off next visit', 'No Coupon') as Coupon FROM tblCustomer;
Create a query using the tblCustomer table. Display CustomerID, FirstName, LastName, and a new calculated field...
(TCO 7) Write a query to display the orderid, order date, customer last name and firstname for all orders that have shipped. SalesRep Customer ReplD int PK CustomerID int LastName FirstName Commission Rate varchar(20) varchar(20) decimal(10,2) LastName FirstName Street City State Zipcode Balance ReplD varchar(20) varchar(20) varchar(20) varchar(20) char(2) char(5) HO----O decimal(10,2) Order OrderID PK int FK1 int FK1 CustomerID OrderDate ShipDate date date Part PK PartID int Orderline varchar(20) int Description UnitsOnHand Item Class Retail Cost PK,FK1 OrderID PartID...
I need to create a SQL query that displays the names of the customers who purchased the book with the highest retail price in the database. Also I need to capitalize the first and last names. CUSTOMERS Table: CUSTOMER#, LASTNAME, FIRSTNAME, ADDRESS, CITY, STATE, ZIP, REFERRED, REGION, EMAIL ORDERS Table: ORDER#, CUSTOMER#, ORDERDATE, SHIPDATE, SHIPSTREET, SHIPCITY, SHIPSTATE, SHIPZIP, SHIPCOST ORDERITEMS Table: ORDER#, ITEM#, ISBN, QUANTITY, PAIDEACH
Write following queries given these schemas: (FK stands for Foreign Key) Customer = {customerID, firstName, lastName, income, birthDate} Account = {accNumber, type, balance, branchNumberFK-Branch} Owns = {customerIDFK-Customer, accNumberFK-Account} Transactions = {transNumber, accNumberFK-Account, amount} Employee = {sin, firstName, lastName, salary, branchNumberFK-Branch} Branch = {branchNumber, branchName, managerSINFK-Employee, budget} Focus should be on using: Order By to sort data Set Operators to union/intersect multiple tables Join Operator to join multiple tables Aggregations and Group By to aggregate data Subqueries 3.15 Customer ID, first...
Display all customers. If a customer has placed any orders, also
display the highest.....
Tables:
CREATE TABLE Sales.Customers
(
CustomerId
INT
NOT NULL
IDENTITY,
CustomerName
NVARCHAR(50)
NOT NULL,
StreetAddress NVARCHAR(50) NULL,
City
NVARCHAR(20)
NULL,
[State]
NVARCHAR(20)
NULL,
PostalCode
NVARCHAR(10)
NULL,
Country
NVARCHAR(20)
NULL,
Contact
NVARCHAR(50)
NULL,
Email
NVARCHAR(50)
NULL,
CONSTRAINT PK_Customers PRIMARY KEY(CustomerId)
);
CREATE TABLE HR.Employees
(
EmployeeId
INT
NOT NULL
IDENTITY,
FirstName
NVARCHAR(50) NOT
NULL,
LastName
NVARCHAR(50)
NOT NULL,
BirthDate DATE
NOT NULL,
HireDate
DATE
NOT NULL,
HomeAddress...
Start Access. Open the downloaded Access file named exploring_acap_grader_a1_Loans.accdb. Import the exploring_acap_grader_a1_Clients.xlsx Excel workbook into a table named Clients. While importing the data, make sure to select the option First Row Contains Column Headings, and select ClientID as the primary key field. After you have completed the import, open the Clients table in Design view. Change the ClientID field size to 6 and remove the @ symbol from the ClientID format property. Change the ZIP field size to 5. Change...
can someone solve these quick
please and thank you!
sql chapter 4
Query #1: List the company name, contact name, contact title and
the phone number for customers who HAVE NOT put in an order. Use an
outer join.
Query #2: Create a listing displaying the employee first name,
last name and the full name (First name space Last Name) of the
person they report to (Supervisor). This is a self-join. If an
employee does not report to anyone then...
Please help me on the SQL
queries, thank you so much.
Write a query to display the
title and publisher as well as the publisher contact for each book
using JOIN...USING clause.
Write a query to show the
first and last names of customers who have ordered cooking books.
Use the WHERE clause to join the tables.
Write a query to show the
title, cost and ISBN of each book in the books table. If the book
has been ordered,...
Write an SQL query against the Chinook Database v1.4. Each query
must run successfully using DB Browser for SQLite. Create the exact
query below. Must use sub query. I saw that a
similar solution for this question used IF and CONCAT functions
which do not work and return errors. If it is possible to use the
CASE function in place of IF that would be greatly appreciated.
Also I really hate to ask but if it could be organized neatly...
I am using oracle sql developer to create some queries to generated reports and it is not working. I am not sure how to use count, group by, and order by. Help me fix my query and explain to me how you did, so can do it next time. Also, I have to convert this query to a stored procedure, so can you help me do it too? Here is my query: SELECT COUNT(GUEST.FIRSTNAME), GUEST.FIRSTNAME, GUEST.LASTNAME, GUEST.GUESTTYPE, RESERVATION.RESERVATIONDATE, GUEST.EMAIL, FROM...
I need help for SQL homework.
the question:
the code for part 1,2:
drop table Customer;
drop table Company;
drop table Cruise;
drop table TravelAgent;
drop table Reservation;
drop sequence customerID_seq;
drop sequence cruiseID_seq;
drop sequence travelAgentID_seq;
drop sequence reservationID_seq;
create table Customer(
customerID number,
firstName varchar2(15),
lastName varchar2(15),
address varchar2(30),
phone number(10) not null,
age number(3),
Constraint Customer_PK Primary Key (customerID),
constraint Customer_unique unique (firstName,lastName,phone),
constraint Customer_check check(phone is not null)
);
create sequence customerID_seq
start with 1
increment...