How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site. Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Eljon Your Own Question
Eljon
Eljon, Consultant
Category: Programming
Satisfied Customers: 595
Experience:  Founder of StockCanvas.com
9260532
Type Your Programming Question Here...
Eljon is online now
A new question is answered every 9 seconds

I have an Access database file and I have to write some SQL

This answer was rated:

I have an Access database file and I have to write some SQL statements in the database. I'm having trouble at the moment trying to understand how to write the statements. I would greatly appreciate some help on this if possible. >:)
Hi my name isXXXXX for using JustAnswer.

To be clear are you looking for help on writing certain types of SQL queries or do you need to know where and how you do that?
Customer: replied 5 years ago.

Here are the statements that need to be conducted below, but I am having trouble attaching the database file. How do I link the .dbs file here?

 

  • 1. Write a SQL statement to display Students' First and Last Name.
  • 2. Write a SQL statement to display the Major of students with no duplications. Do not display student names.
  • 3. Write a SQL statement to display the First and Last Name of students who live in the Zip code 88888.
  • 4. Write a SQL statement to display the First and Last Name of students who live in the Zip code 88888 and have the major of Biology.
  • 5. Write a SQL statement to display the First and Last Name of students who live in the Zip code 88888 or 88808. Do not use IN.
  • 6. Write a SQL statement to display the First and Last Name of students who have the major of Biology or Math. Use the SQL command IN.
  • 7. Write a SQL statement to display the First and Last Name of students who have the Status greater than 1 and less than 10. Use the SQL command BETWEEN.
  • 8. Write a SQL statement to display the First and Last Name of students who have last name that starts with an S.
  • 9. Write a SQL statement to display the First and Last Name of student having an ‘a' in the second position in their first names.
  • 10. Write a SQL expression to display the Status and a sum of the Status of each Status value as SumOfStatus. Group by Status and display the results in descending order of SumOfStatus.
Click on the paper clip Icon. If that does not work upload the file to wikisend.com and paste the URL of the upload here so I can download it.

When do you need this?
Customer: replied 5 years ago.

http://wikisend.com/download/130582/Lab_1_SQL.accdb

There's the link to download the .dbs file. I am needing it by Sunday if that is ok with you?

That works for me. I'll let you know when it's ready.
Customer: replied 5 years ago.
Thanks Eljon. Oh and btw, how do i cancel the post I sent to Carla (which was another programmer that I asked the same question to)? I didn't know it would deduct money from me twice and I can't figure out how to cancel the other one out =\
Send me the link to your other question and I'll send it to the moderators so that they can handle it.
Here is the answer. I have provided the database file and the list of queries.

http://www.wikisend.com/download/459204/Lab_1_SQL.accdb

http://pastebin.com/sJyGMkZv

Eljon and 2 other Programming Specialists are ready to help you
Customer: replied 5 years ago.

Eljon,

Thanks again for the assistance. I really appreciate it. One question I have is that how did you construct the queries in Access at all? This might be some simple question I'm asking, but this is where I'm getting confused at the moment. lol

I've included some screenshots...For simple queries screen shot 1 suffices, but for a lot of these you have to go to the SQL view which is an advanced view to write the queries using the SQL language.

Basic


Advanced
Customer: replied 5 years ago.

Yea, I think that's my biggest problem there. I get confused on what to do once I go through the Query Design Wizard. I have no idea how you were able to obtain the queries that I listed for you. Do I have to "run" anything from the answers you provided me earlier though? Or are all the Queries already there? Sorry for the extra questions Eljon, I just want to somewhat get a brief understanding on how you did this :p

You don't need to go through the design wizard. The queries are already in the database I provided. Just open the database, look under Queries and right click on each Query and click on Design View. That'll show you how to design the query.
Customer: replied 5 years ago.
Ohhhh alright. See.... I told you this SQL stuff is problematic for me x_x Thanks again Eljon
No problem.
Customer: replied 5 years ago.

Eljon,

I was wondering if you could help me with Microsoft Visio? Please let me know if you would be able to assist me. Thanks

Sorry I don't use Visio. I'm pretty much a database and programming guy.
Customer: replied 5 years ago.
Ok thanks.
No problem.
Customer: replied 5 years ago.

Hi Eljin,

I had a couple questions regarding constraints and database redesign.

 

First Question: Business requirements are enforced by implementing database constraints on tables and columns. The database constraints available include the following.

  • PRIMARY KEY
  • FOREIGN KEY or REFERENTIAL INTEGRITY
  • NOT NULL
  • UNIQUE
  • CHECK

Give a business requirement and the constraint that could be implemented to enforce it. Explain the constraint you choose, and give the SQL to implement the constraint in the database.

 

What are the long-term problems when these constraints are not implemented?

 

Second Question: After your database was put into production, a design flaw was discovered! One of the relationships modeled as a 1:N relationship was really an N:M relationship. The database issue invoves the following tables, with PK italicized and underlined.

VEHICAL (VIN, Make, Model, Color, LicenceTag)
DRIVERS (DriverLicenseNo, LastName, FirstName, Address, Zip, VIN)
ZIPCODE (Zipcode, City, State)

Which 1:N relationship should be an N:M relationship? How would you redesign the database to correct the issue? What else must you consider when updating the structure of your database tables?



Hi and welcome back. Can you create a new question for this? Thanks.
Customer: replied 5 years ago.
Ok, will do.