Help with writing SQL Statements in Acess

[Chuck_45]

Coming from a background of SQL Server and T-SQL, Access is no my favorite.
Need to know how: In Access

Write an SQL statement
to display the publication ID, publication date, cost, and category of
each book planned for publication ID number 4.
Repeat the previous task but include ID and Date as column aliases for the first two columns.

Write an SQL statement to display the least expensive retail price book in each book category.

Repeat the previous task but sort the displayed categories in the descending order.
Write an SQL statement
to display the customer number, city of destination, and shipping date
for all shipments with order number above 2. (Hint: Use the Orders table.)
Repeat the previous task but further restrict the output to shipping dates after January 1, 2013. (Hint:

The date restriction will require a format identical to the one
displayed in the previous task, and it must be surrounded by pound
signs—#01/01/2013# for example.)

Write an SQL statement
to display the publication ID, average retail price, and average cost of
all books. Group the output by publication ID.
Write INSERT statements to add the following data to the Publisher table:

PubID
Name
Contact
Phone
6
READ WITH US
John Doe
866-888-9999
7
KNOWLEDGE FOR ALL
Jane Doe
800-1212-5555
8
CHEAP PUBLISHING
Tom Smith
000-321-4071
9
TEXT AND MORE
John Carter
032-100-7777
10
BOOK BUSTER
George Stevens
777-555-1212
Write a single SELECT statement that will verify the records inserted.
Write a DELETE statement to remove only the new BOOK BUSTER record

 

[pbaldy]

The basic SQL is generally the same. Why don't you give the homework a shot and we'll see how you're doing? You can use the design grid or write SQL in the SQL window directly, since you're familiar with it.

 

[Chuck_45]

Actually it isn't homework it's a project I am working on using the example to practice on but Thank you.

 

[Chuck_45]

I already have the first part done having a problem with the last part;

• Write an SQL statement to display the publication ID, average retail price, and average cost of all books. Group the output by publication ID.
• Write INSERT statements to add the following data to the Publisher table:
  PubID​
  Name​
  Contact​
  Phone​
  6 READ WITH US John Doe 866-888-9999 7 KNOWLEDGE FOR ALL Jane Doe 800-1212-5555 8 CHEAP PUBLISHING Tom Smith 000-321-4071 9 TEXT AND MORE John Carter 032-100-7777 10 BOOK BUSTER George Stevens 777-555-1212
• Write a single SELECT statement that will verify the records inserted.
• Write a DELETE statement to remove only the new BOOK BUSTER record.

 

[pbaldy]

Your background in T-SQL should make it easy. There are differences, so dive in and see how it goes.

 

[pbaldy]

What exactly are you having trouble with? What have you got so far? One note, you can't stack multiple inserts in Access like you can in t-SQL. Each would be a separate query.

 

[Chuck_45]

For sure doesn't hurt to ask for help. Access is being used where I am working with similar inputs alreardy got the statement in the query just that last part as I indicted.


INSERT INTO Publisher (PubId, PubName, Contact, Phone)
VALUES (‘6’,’READ WITH US’,’John Doe’,’866-888-9999’);

INSERT INTO Publisher (PubId, PubName, Contact, Phone)
VALUES (‘7’,’KNOWLEDGE FOR ALL’,’Jane Doe’,’800-1212-5555’);

etc.

Then later...

UPDATE Publisher SET Contact = 'John Travolta'
WHERE Contact='John Pearson';

UPDATE Publisher SET Phone = '(888) 999-7777'
WHERE Phone='(123) 555-7878';

 

[Chuck_45]

Working on this right now;

• Write an SQL statement to display the publication ID, average retail price, and average cost of all books. Group the output by publication ID.
• Write INSERT statements to add the following data to the Publisher table:
  PubID​
  Name​
  Contact​
  Phone​
  6 READ WITH US John Doe 866-888-9999 7 KNOWLEDGE FOR ALL Jane Doe 800-1212-5555 8 CHEAP PUBLISHING Tom Smith 000-321-4071 9 TEXT AND MORE John Carter 032-100-7777 10 BOOK BUSTER George Stevens 777-555-1212
• Write a single SELECT statement that will verify the records inserted.
• Write a DELETE statement to remove only the new BOOK BUSTER record.

 

[Chuck_45]

I got the solution
INSERT INTO Publisher (PubId, PubName, Contact, Phone)
VALUES (‘6’,’READ WITH US’,’John Doe’,’866-888-9999’);

INSERT INTO Publisher (PubId, PubName, Contact, Phone)
VALUES (‘7’,’KNOWLEDGE FOR ALL’,’Jane Doe’,’800-1212-5555’);

INSERT INTO Publisher (PubId, PubName, Contact, Phone)
VALUES (‘8’,’CHEAP PUBLISHING’,’Tom Smith’,’000-321-4071’);

NSERT INTO Publisher (PubId, PubName, Contact, Phone)
VALUES (‘9’,’TEXT AND MORE’,’John Carter’,’032-100-7777’);

NSERT INTO Publisher (PubId, PubName, Contact, Phone)
VALUES (‘10’,’BOOK BUSTER’,’George Stevens’,’777-555-1212’);

The last part I am not getting:

• Write a single SELECT statement that will verify the records inserted.
• Write a DELETE statement to remove only the new BOOK BUSTER record.

 

[Chuck_45]

See actually in access is it is wrote
INSERT INTO Publisher (PubName, Contact, Phone) VALUES ("READ WITH US", "John Doe", "866-888-9999");

not INSERT INTO Publisher (PubId, PubName, Contact, Phone)
VALUES (‘6’,’READ WITH US’,’John Doe’,’866-888-9999’);

 

[pbaldy]

See actually in access is it is wrote
INSERT INTO Publisher (PubName, Contact, Phone) VALUES ("READ WITH US", "John Doe", "866-888-9999");

not INSERT INTO Publisher (PubId, PubName, Contact, Phone)
VALUES (‘6’,’READ WITH US’,’John Doe’,’866-888-9999’);

Only if PubId is an autonumber field (Identity in SQL Server). That's not really a syntax difference, as you wouldn't normally specify that field in SQL Server either (and it should work with either quotes in Access).

As to the last 2, SELECT and DELETE queries with the appropriate criteria would solve the problems, I would think.

 

[Chuck_45]

Should work but doesn't any way I have it figured out, you would know this if you wrote in in the Query only to get a Syntax error.

 

[pbaldy]

If you got a syntax error with the "not" version, it was likely because PubId is a numeric field, and thus the 6 shouldn't have any quote delimiters around it.