-1

I'm new with SQL and just had my first assignment.

I have the following requirements: Given is a database of two tables. The first one contains information about the user, like a unique ID per user, the phone number and the city. ID and phone number consist only of numeric digits. The second table contains data about so called „credits“, which a user can own. Again there is a column for the unique user ID, but also the number, the date and the type of credits. A user can have none, one or several entries in the credit table.

I'm still now sure if I got right the part where a user can have none, one or several entries in the credit table. I created these two tables:

CREATE table user
(

 user_id INT NOT NULL UNIQUE AUTO_INCREMENT,

 user_phone_number INT NOT NULL,

 user_city VARCHAR(32) NOT NULL,

 PRIMARY KEY (user_id)
 );

CREATE table credit
(
 credit_user_id INT FOREIGN KEY (user_id),

 credit_date date,

 credit_number double,

 credit_type char(10),

   CONSTRAINT chk_type CHECK (credit_type in ('None','A','B','C')),
 );

After creating this, I was asked the following questions:

a) The phone number of all users, who own credits of type „A“

SELECT user_phone_number 
FROM user, credit
WHERE credit_type = 'A';

b) Like a), but additionally the credit_number of the credits is smaller than 2 or greater than 4

SELECT user_phone_number
FROM user, credit
WHERE (credit_type ='A')
AND (credit_number < 2 OR credit_number > 4);

C) Like a), but additionally the users also own credits of at least one other type.

SELECT user_phone_number 
FROM user, credit
WHERE credit_type = 'A'
AND (
SELECT DISTINCT c1.credit_type FROM credit AS c1
JOIN credit a1 ON (c1.credit_type=a1.credit_type)
JOIN credit a2 ON (c1.credit_type=a2.credit_type)
WHERE a2.credit_type<>a1.credit_type);

My problem is that I can't make letter C work, even if both selects seem to work separately. Any ideas or suggestions would be appreciated, thank you!

Poly Tibets Sergueevna
  • 17
  • 2
  • 1
    I would strongly suggest that you teach yourself the basics of SQL before tackling something. An on line search with the keywords "SQL Beginner's Tutorial" will bring you at least half a dozen possibilities to train yourself on-line on the basics. After that, you'll see what's wrong with your approach. – marcothesane Feb 13 '17 at 11:26
  • *Never* use commas in the `FROM` clause. *Always* use explicit `JOIN` syntax. All your answers are wrong. Hence, I'm voting to close as too broad. – Gordon Linoff Feb 13 '17 at 11:49
  • This is actually from an online SQL basics course I'm taking to learn more, I will continue searching for more tutorials. Thank you – Poly Tibets Sergueevna Feb 13 '17 at 16:05

3 Answers3

0

I'm not sure to understand what you want in C) but many things to say.

  • You should't use a database name like 'user' because it can be ambigous (reserved word) for SGBD.

  • You should prefer 'join' instead of 'from table1, table2' and / or mix both. Have a look here.

  • You've got ';' in your request in C) which must be only for specify the end of your request.

  • You can use nested resquet but not like that, not directly after 'AND' because AND is for condition like a comparison. You've got many possibilites : in select fied, after 'FROM', after 'IN', with join, in condition... Quick search on google.

Community
  • 1
  • 1
Xavier Lambros
  • 776
  • 11
  • 19
0

From another post:

PRIMARY KEY(x), UNIQUE(x) -- Since a PRIMARY KEY is by definition (in MySQL) UNIQUE...

Since you want to find everyone with 2 kinds of credit, I'd try to make a query like if I was looking for duplicates, here's two ways to do that: With subquery Find duplicate records in MySQL Without Finding duplicate values in MySQL

Community
  • 1
  • 1
Jan Christoffersen
  • 103
  • 7
0

Welcome to SO! Here's an approach using the nested query style you're trying to use. I've used explicit JOINs rather than FROM user, credit in the FROM clause, because this makes it clearer that it's a join.

Say your users table looks like this -

user_id     user_phone_number   user_city
6           75771               Leeds
7           75772               Wakefield
8           75773               Dewsbury
9           75774               Heckmondwike
10          75775               Huddersfield

And your credit table looks like this -

credit_user_id      credit_date     credit_number   credit_type
7                   2017-02-13      2               A
7                   2017-02-13      2               B
6                   2017-02-13      2               A
8                   2017-02-13      4               B

The nested query in the AND clause returns records where the credit_type is not A, and the WHERE in the main query selects all records where the credit_type is is A, so if the record appears in both, the user must have two types of credit -

SELECT user_phone_number 
FROM [user] AS u
JOIN credit AS c ON u.user_id = c.credit_user_id
WHERE credit_type = 'A'
AND u.user_id IN (
    SELECT user_id
    FROM [user] AS u
    JOIN credit AS c ON u.user_id = c.credit_user_id
    WHERE credit_type <> 'A')

As you can see from the tables, the user with the id of 7 has credit both of type A and B, so we end up with -

user_phone_number
75772

I'd agree that you might want to consider some of the points others have raised above, but won't repeat.

Parrybird
  • 800
  • 11
  • 18