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:  11 yrs of programming (PHP, WordPress, XSL, SQL, JavaScript)
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

Customer Question

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. >:)
Submitted: 2 years ago.
Category: Programming
Expert:  Eljon replied 2 years ago.
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 2 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.
Expert:  Eljon replied 2 years ago.
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 2 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?

Expert:  Eljon replied 2 years ago.
That works for me. I'll let you know when it's ready.
Customer: replied 2 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 =\
Expert:  Eljon replied 2 years ago.
Send me the link to your other question and I'll send it to the moderators so that they can handle it.
Expert:  Eljon replied 2 years ago.
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, Consultant
Category: Programming
Satisfied Customers: 595
Experience: 11 yrs of programming (PHP, WordPress, XSL, SQL, JavaScript)
Eljon and other Programming Specialists are ready to help you
Customer: replied 2 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

Expert:  Eljon replied 2 years ago.
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

Attachments are only available to registered users.

Register Here

Attachments are only available to registered users.

Register Here

Attachments are only available to registered users.

Register Here
View Full Image

Attachments are only available to registered users.

Register Here


Advanced

Attachments are only available to registered users.

Register Here

Attachments are only available to registered users.

Register Here

Attachments are only available to registered users.

Register Here
View Full Image

Attachments are only available to registered users.

Register Here
Customer: replied 2 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

Expert:  Eljon replied 2 years ago.
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 2 years ago.
Ohhhh alright. See.... I told you this SQL stuff is problematic for me x_x Thanks again Eljon
Expert:  Eljon replied 2 years ago.
No problem.
Customer: replied 2 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

Expert:  Eljon replied 2 years ago.
Sorry I don't use Visio. I'm pretty much a database and programming guy.
Customer: replied 2 years ago.
Ok thanks.
Expert:  Eljon replied 2 years ago.
No problem.
Customer: replied 2 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?



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

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Last | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
  • Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
 
 
 

Meet The Experts:

 
 
 
  • ATLPROG

    Computer Software Engineer

    Satisfied Customers:

    7463
    MS in IT.Several years of programming experience in Java C++ C C# Python VB Javascript HTML
< Last | Next >
  • http://ww2.justanswer.com/uploads/SP/spatlanta2010/2011-6-23_12450_photo.64x64.gif ATLPROG's Avatar

    ATLPROG

    Computer Software Engineer

    Satisfied Customers:

    7463
    MS in IT.Several years of programming experience in Java C++ C C# Python VB Javascript HTML
  • http://ww2.justanswer.com/uploads/ComputersGuru/2010-02-13_051118_Photo41.JPG LogicPro's Avatar

    LogicPro

    Computer Software Engineer

    Satisfied Customers:

    5603
    Expert in C, C++, Java, DOT NET, Python, HTML, Javascript, Design.
  • http://ww2.justanswer.com/uploads/unvadim/2010-11-15_210218_avatar.jpg unvadim's Avatar

    unvadim

    Computer Software Engineer

    Satisfied Customers:

    1158
    Good knowledge of OOP principles. 3+ years of programming experience with Java and C++. Sun Certified Java Programmer 5.0.
  • http://ww2.justanswer.com/uploads/lifesaver333/2010-10-17_191349_ls.jpeg lifesaver's Avatar

    lifesaver

    Computer Software Engineer

    Satisfied Customers:

    950
    Several years of intensive programming and application development experience in various platforms.
  • http://ww2.justanswer.com/uploads/EH/ehabtutor/2012-8-2_202016_1.64x64.jpg ehabtutor's Avatar

    ehabtutor

    Computer Software Engineer

    Satisfied Customers:

    864
    Bachelor of computer science, 5+ years experience in software development, software company owner
  • http://ww2.justanswer.com/uploads/RA/rajivsharma086/2012-6-6_17128_displaypic.64x64.jpg Raj's Avatar

    Raj

    Computer Engg.

    Satisfied Customers:

    860
    BE CS, 4+ Experience in Programming and Database (ERP)
  • http://ww2.justanswer.com/uploads/eljonis/2010-01-06_130406_eljon2.jpg Eljon's Avatar

    Eljon

    Consultant

    Satisfied Customers:

    590
    11 yrs of programming (PHP, WordPress, XSL, SQL, JavaScript)
 
 
 
Chat Now With A Programmer
Eljon
Eljon
Programmer
595 Satisfied Customers
11 yrs of programming (PHP, WordPress, XSL, SQL, JavaScript)