MSSMS How to select using functions and using agregations

1

I have table 1 :

  • CREATE TABLE Zamestnanci
    (id_Zamestnanca int not null,
    Meno varchar(50) null,
    Priezvisko varchar(50)null,
    Adresa varchar(50) null,
    Datum_narodenia date null,
    PRIMARY KEY (id_Zamestnanca)
    );*

Table 2 :

  • CREATE TABLE ObčaniaSR
    (id_Občana int not null,
    Meno varchar(50) null,
    Priezvisko varchar(50) null,
    Adresa varchar(50) null,
    Datum_narodenia date null,
    Zápis_v_trestnom_registry varchar(50) null,
    PRIMARY KEY (id_Občana)
    );*

Table 3 :
*CREATE TABLE Register_priestupkov
(id_Priestupku int,
Dátum_platnosti_priestupku date,
Popis_priestupku nvarchar (150),
Trestná_sadzba_za_priestupok money,
PRIMARY KEY (id_Priestupku)

);*

Table 4 :
CREATE TABLE Priestupky
(id_Priestupku int,
id_Občana int,
id_Zamestnanca int,
dátum_spáchania_priestupku date,
Zápis_v_trestnom_registry date,
id_Trestnej_sadzby int,
PRIMARY KEY (dátum_spáchania_priestupku),
FOREIGN KEY (id_Občana) REFERENCES Občania_SR (id_Občana) ON DELETE CASCADE,
CONSTRAINT fkid_Občana FOREIGN KEY (id_Občana) REFERENCES Občania_SR (id_Občana),
FOREIGN KEY (id_Zamestnanca) REFERENCES ZAMESTNANCI (id_Zamestnanca) ON DELETE CASCADE,
CONSTRAINT fkid_Zamestnanca FOREIGN KEY (id_Zamestnanca) REFERENCES Zamestnanci (id_Zamestnanca),
FOREIGN KEY (id_Priestupku) REFERENCES Register_priestupkov (id_Priestupku) ON DELETE CASCADE,
CONSTRAINT fkid_Priestupku FOREIGN KEY (id_Priestupku) REFERENCES Register_priestupkov (id_Priestupku),

How can I select from this tablets using functions and using agregations? Can you please give me an example? Thank you for your answers and advices
2

So is this work related or schooling related?

3

It 's university project. Why are you asking?

4

so generally most people here would rather you work out the solution yourself and post your questions when you are stuck especially if it is a school project.

otherwise how can you learn when others are doing it for you?
trying it on your own will help you grow your knowledge exponentially.

So I personally would encourage you to do your research here for example below for sum (aggregation) and try it on your db and tables, then when you truly stuck come back and we would be happy to help you

5

I've tryied this function
*Create function Vyber
returns Zamestnanci
as
return(SELECT * FROM Zamestnanci);
*
i got this error:
*Msg 8117, Level 16, State 1, Line 3
Operand data type date is invalid for avg operator.
Msg 209, Level 16, State 1, Line 3
Ambiguous column name 'Datum_narodenia'.
*

6

and also tried this : SELECT Meno
FROM Zamestnanci
INNER JOIN ObčaniaSR
on Meno = 'Jan';

I tried to SELECT from Zamestnanci and ObcaniaSR Where Meno is Jan
but I got :

*Msg 209, Level 16, State 1, Line 6
Ambiguous column name 'Meno'.
Msg 209, Level 16, State 1, Line 3
Ambiguous column name 'Meno'.
*

7

here is an example from MS you can use to learn how to create functions.

IF OBJECT_ID (N'dbo.ufnGetInventoryStock', N'FN') IS NOT NULL  
    DROP FUNCTION ufnGetInventoryStock;  
GO  
CREATE FUNCTION dbo.ufnGetInventoryStock(@ProductID int)  
RETURNS int   
AS   
-- Returns the stock level for the product.  
BEGIN  
    DECLARE @ret int;  
    SELECT @ret = SUM(p.Quantity)   
    FROM Production.ProductInventory p   
    WHERE p.ProductID = @ProductID   
        AND p.LocationID = '6';  
     IF (@ret IS NULL)   
        SET @ret = 0;  
    RETURN @ret;  
END;
8

The problem here is that you are not using table aliases - or referencing the columns by table. You can review this for further help: SQL Aliases.

From your previous post - you are trying to get the average of a date/time column. It doesn't make sense to do that...but from another post I think you want to get the average age, which is going to be based on that date column.

To do that, you have to calculate the age first - then you can get the average. To calculate a persons age - you first take the difference between their date of birth and today in years, then if the month and day of the date of birth is less than the month and day of today - subtract 1 (they haven't reached their birthday yet).

Before trying to create functions - you need to understand SQL first, and you really need to have working and tested code before wrapping it in a function.