-1

I have IT homework that is due at midnight tonight. For our assignment, we had to create a table in an SQL database off my school AFS database. I am using MobaXTerm to do this homework assignment.

I created a table name "student". I created the entire table correctly. It is correct, because my professor gave me the exact command to create it. Here are the columns in order: id, firstname, lastname, address, state, gpa, credits. I populated this table with 20 students, however I do not want to post the picture of the result on here, because it has personal info on it.

I answered the other questions correctly, however I am stuck on this question that has multiple questions in it:

  1. Next, write and run (issue) SQL queries that do the following. For each query, provide screenshots for the SQL query and the results within a Word document so I can grade it.

a. Show state and gpa information about students with the first name Peter (I was told to add students with the name "Peter" before I created this table). This one is correct here is the command i used: select state, gpa, firstname from student where lastname = 'Peter';

b. Retrieve the last names, state, and credits of all students that are NOT from AZ or FL. Order by the state.

I am struggling on this one, because I do not know how to show the table of students that are both NOT from AZ and FL.

But here is a command that worked to show if they are not from one state. select, lastname, credits, state from student where state != 'AZ'

How am I supposed to write that student is not equal to both AZ and FL?

c. How many students live on '10 Main Street'?

select id, address from student where address='10 Main Street';

This question is correct.

d. Retrieve all sophomore student ids along with their credits that are NOT C students (see the table for definition for “sophomore” and “C” grades).

So the table shows that a sophomore has 33-64 credits. A C student has a GPA of 1.7-2.69. So what is my line of command to show these range of numbers?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
potanta
  • 43
  • 2
  • 8

1 Answers1

0

Q: How am I supposed to write that student is not equal to both AZ and FL?

... WHERE state != 'AZ' AND state != 'FL'

Q: How many students ...

SELECT COUNT(*) AS count_students FROM ...

Q: Sophmore not C

... WHERE credits >= 33 AND credits <= 64
      AND NOT ( gpa >= 1.7 AND gpa <= 2.69)
spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • As i written above, I already said that the How many students question is correct. – potanta Dec 10 '16 at 00:18
  • 1
    It's not. @spencer7593 is. – Chris Dec 10 '16 at 00:20
  • @spencer7593 actually the answer i had there was right. the results came out correct. It shows all students that live on Main Street. The id part was just to number the students so i can count them. – potanta Dec 10 '16 at 00:25
  • And also here is another question i am struggling on: e. Determine if there are any students from CT with at least a ‘C’ or if there are any AZ students that have a ‘seniors’. Show their lastname and State. Order the information by state. – potanta Dec 10 '16 at 01:06
  • @potanta: I recommend you review some basic elements of SQL comparison operators... **`>`**, **`>=`**, **`=`** (and how those operate on a variety of datatypes), and logical operators **`NOT`**, **`AND`**, **`OR`**, and using parentheses to specify the sequence of operations (over the normal order of precedence). If I want write a query that answers the question "how many", I am very apt to use an aggregate function e.g. `SUM(1)`, `COUNT(DISTINCT id)`, etc. You may consider a list of id values as an "already correct" answer to "how many". But I don't. – spencer7593 Dec 10 '16 at 05:12