Help begginer SQL for school! few querrys

Transact-SQL
1

yHp3G.jpg1434×1076 546 KB

I have these 2 tables (1st let's say table1 and 2nd table2) I need to do the follwings operations on these 2 tables for a project tomorrow! If anyone could help, would be much appreciated! I need em in querry so I can copy paste it for project!

1.Show all from "Parinti" that have "Angajator" as "Firma1"

2.Show all from "Copil" that have "Parinte1"

3.Update the field "Data_creare" from table2 with current date for "Copil" that have "Parent1"

4.Delete all from "Copil" that have "Data_Naster" = 10.01.2013

5.last I need to sort the values from table2 column "Copil" ascending ascending depending of field "Data_nastere"

2

You'll have to at least try to solve it yourself (you learn nothing from copy paste).
Write again if you get stuck (show us what you've tried and whats now working), and we'll try to point you in the right direction.

3

  1. SELECT Parinte FROM table1 WHERE Angajator = "Firma1"

  2. SELECT table2.Copil, table1.Parinte
    FROM Table2
    INNER JOIN Table1
    ON tabel2.Id_Parinte = Tabel1. (and now I don't know if it's ID or Parinte!!!!!!!!!!!)

3.UPDATE table2 SET Data_creare = GETDATE () WHERE ID_Parinte = 1 (don't know if it's right)
4. I don't know how to filter/sort by date
5. same as 4

Any help would be much appreciated!

4
  1. almost correct, just replace double quotes with single quotes
  2. you know that you need "inner join" - excelent. this shows how it works.
  3. this will work, but only because you already know id_parinte is 1. you would want probably want to join to table 1 thru the id, to find parinte field = 'Parinte1'. to do this you could use the "exists" clause in the where section of your update statement. this shows how it could be done
  4. if field is og type date, you can use cast like this: where data_naster=cast('2013-01-10' as date)
  5. sorting is done with the "order by" clause. see here

Finally you would want to test your queries - Get you own copy of mssql og do it online here

1 Like
5

For No.2 is this ok?

SELECT Parinte
FROM table1
INNER JOIN table2
ON table1.Id = table2.Id_Parinte

6
  1. correction: you want to show all field, so either list all fieldnames or use "select *"
  2. this is correct way of joining. you just need to specify fields you want to see (or select *) and that you want records/rows where parinte is "Parinte1"
7

I give up, I aleready mixed up the little that I know! and I am screw-ing up everything! why won't you just write it down bitsmed!, I am sure it takes you 5 mins to make all the points correct !And only 6 hours till I have to go to school!

8

You didn't screw up - I think you were doing well. We all have to learn, but we don't learn anything by copy/paste. I think it's a pitty that you give up, but hey - If you don't care, why should I

9

I think i got it for no.2 it returns as expected! 'Copil1' 'Copil2'

SELECT Copil
FROM tabel1
INNER JOIN tabel2
ON tabel1.Id = tabel2.Id_Parinte
WHERE Parinte = 'Parinte1'

Any suggestions for the remaining pts?

10

I Have solved no 3 and 4 , but i am really stuck at point no.5 the last one!

SELECT MIN(Copil) FROM tabel2 does not return the desired value

in my tab Data_Nasterii is Birthday and i need to show the smallest Copil by age!

This:
SELECT Copil FROM tabel2
ORDER BY Data_Nastere DESC

Shows all descending, but i only need to show Copil4

11

You could do:

select top(1) copil
  from table2
 order by data_nastere desc
;
12

Yes, but it only needs to return from Copil, Copil4 wich is the smallest based on Data_nastere!

Dunno how to explain....Copil is Kid and Data_nastere is birthday, i need to show the youngest kid(the kid that has the smallest age)

13

That is exactly what it does, when using top(1).
Is show the first one according to your "order by" clause.
Try it.

14

Gives error in syntax
Error Code: 1305
FUNCTION testingtab.top does not exist

15

Which database engine and version are you using?

16

i think i did it!
SELECT Id, Copil, Data_Nastere, Id_Parinte
FROM tabel2
WHERE Data_Nastere = (SELECT MAX(Data_Nastere) FROM Tabel2)

this returns exactlly what i need! :smiley:

Tyvm bitsmed for the help and motivation! also can i ask you if this statement is correct

DELETE FROM tabel2
WHERE Data_Nastere = '10.01.2013'
for No4 or would you do it differently?

17

Your solution does also jork - nice job :+1:
The error you got before when using "top" could indicate you're using other database engine as mssql. If you are using mysql, the equivalent method of top is limit:

select copil
  from table2
 order by data_nastere desc
 limit 1
;

I would use international date standard (I think it's ISO 8601) which defines date as yyyy-mm-dd. So I would do:

delete from table2
 where data_nastere=cast('2013-01-10' as date)
1 Like