--Sentencias SQL generadas en Clase por Alumnos --

CONSULTAS

-- muestra una lista de los clientes con sus cuentas y datos.
SELECT T1.dni,T1.nombre,T1.telef,T1.direccion,T2.num_cuenta FROM carla_clientes AS T1 JOIN carla_cuenta_cliente AS T2 ON T1.dni=T2.dni_cliente; 


-- muestra una lista de las transacciones, cantidad, fecha, cuenta origen y destino
SELECT T1.num,T1.fecha,T1.cantidad,T2.cuenta_origen,T2.cuenta_destino FROM carla_transac AS T1 JOIN carla_lista_transac AS T2 ON T1.num=T2.num_transac; 

SELECT T1.id,T1.direccion,T2.num,T2.cantidad FROM carla_sucursal AS T1 JOIN carla_cuentas AS T2 ON T1.id=T2.id_sucursal; --> muestra una lista de las cuentas en cada sucursal.
SELECT empresa, avg(salario)::numeric(10,2), stddev(salario)::numeric(10,2) FROM tabla_ejemplo GROUP BY empresa;
SELECT max(cantidad),id_sucursal FROM carla_cuentas GROUP BY id_sucursal;
SELECT  AVG(salario) FROM tabla_ejemplo;
SELECT empresa, MIN(precio) FROM tabla_ejemplo GROUP BY empresa;
SELECT STDDEV(salario) FROM tabla_ejemplo; 

Actualización de datos:
--Días trabajados el presente mes --
UPDATE empleados SET dias_trabajados=(to_char(now(),'DD'))::integer;

--Todos los empleados cuyo apellido contiene "dottir" son de nacionalidad Islandesa
UPDATE empleados SET nacionalidad='Islandia' WHERE apellido~'dottir';

--Añade en nacionalidad "(Pontevedra)" si es el prefijo del teléfono es 986
UPDATE empleados SET nacionalidad=nacionalidad||'(Pontevedra)' WHERE telefono>=986000000;

--Borra de tabla_ejemplo TODAS las tuplas con el campo salario igual a 100
DELETE FROM tabla_ejemplo WHERE salario=100:


Expresiones:

SELECT 'Hola' || 'soy yo ' || 'estudio ASI';
SELECT 2*20/4::numeric;
SELECT log(45)*2::integer || ' ' || 'este es el resultado'; 
SELECT sqrt(25*951)/4; 
SELECT current_date;
SELECT now();
SELECT lower ('JOSE MANUEL');
SELECT to_char (current_timestamp,'HH12:MI:SS');


Expresiones en Condiciones

SELECT * FROM carla_clientes WHERE nombre ~ '^A.p';
SELECT * FROM carla_transac WHERE fecha ~ '2005-10-..';
SELECT * FROM carla_clientes WHERE direccion ~ 'C/.+';


--Sentencias SQL de la plataforma openacs.org. Más código en http://cvs.openacs.org/ --


-- Ejemplos de Creación de Tablas.  --

CREATE TABLE cal_items (
          -- primary key
        cal_item_id	  integer 
			  constraint cal_item_cal_item_id_fk 
                          references acs_events
                          constraint cal_item_cal_item_id_pk 
                          primary key,            
          -- a references to calendar
          -- Each cal_item is owned by one calendar
        on_which_calendar integer
                          constraint cal_item_which_cal_fk
                          references calendars
                          on delete cascade,
        item_type_id            integer,
        constraint cal_items_type_fk
        foreign key (on_which_calendar, item_type_id)
        references cal_item_types(calendar_id, item_type_id)
);



  -- Table cal_party_prefs stores customization information
  -- for each calendar. These data are unique to each party. 
  -- This means that each party using the same calendar can 
  -- have a different look to it. 
create table cal_party_prefs (
          -- refers to a particular calendar Id
        calendar_id             integer
                                constraint cal_pty_prefs_cal_id_fk
                                references calendars
                                on delete cascade,       
           -- Party Id
        party_id                integer
                                constraint cal_pty_prefs_party_id_fk  
                                references parties
                                on delete cascade,
          -- default_view stores whether the user wants 
          -- list, month, day, week, or year as his/her default view.
        default_view            varchar(10)
                                default 'day'
                                constraint cal_pty_prefs_default_view_ck
                                check (default_view in (
					'list', 
                                        'day',  
                                        'week', 
                                        'month', 
                                        'year'
                                        )
                                ),                              
          -- the default number of minutes for each appointment
        default_duration        integer
                                default 60
                                constraint cal_pty_prefs_default_duration
                                check (default_duration > 0),
          -- the default starting time in daily view in military time 00 - 23
        daily_start             --number(2)
				numeric(2,0) 
                                default 07
                                constraint cal_pty_prefs_daily_start
                                check (daily_start < 24 and daily_start > -1),
          -- the default ending time in daily view in military time 00 -23
        daily_end               --number(2)
				numeric(2,0)
                                default 18
                                constraint cal_pty_prefs_daily_end 
                                check (daily_end < 24 and daily_end > 0),
          -- which time zone does the user belong to
        time_zone               integer 
                                constraint cal_pty_prefs_time_zone_fk
                                references timezones
                                on delete cascade
				check (time_zone > 0),
          -- which day to start the week, monday or sunday
        first_day_of_week       varchar(9)
                                default 'Sunday'
                                constraint cal_pty_prefs_1st_day_ck
                                check (first_day_of_week in (
				        'Sunday', 
                                        'Monday', 
                                        'Tuesday', 
                                        'Wednesday', 
                                        'Thursday', 
                                        'Friday', 
                                        'Saturday'
                                        )
                                ),
          -- unique constraint between calendar_id and party_id
          -- this ensures that each party has only one set of 
          -- perferences per calendar
        constraint cal_party_prefs_un unique(calendar_id, party_id)
);

-- Calendar is a collection of events. Each calendar must
-- belong to somebody (a party).
create table calendars (
          -- primary key
        calendar_id             integer         
                                constraint calendars_calendar_id_fk 
                                references acs_objects
                                constraint calendars_calendar_id_pk 
                                primary key,
          -- the name of the calendar
        calendar_name           varchar(200),
          -- the individual or party that owns the calendar        
        owner_id                integer
                                constraint calendars_calendar_owner_id_fk 
                                references parties
                                on delete cascade,
          -- keep track of package instances
        package_id              integer
                                constraint calendars_package_id_fk
                                references apm_packages(package_id)
                                on delete cascade,
          -- whether or not the calendar is a private personal calendar or a 
          -- public calendar. 
        private_p               boolean
                                default 'f'
                                constraint calendars_private_p_ck 
                                check (private_p in ( 
                                        't',
                                        'f'
                                        )
                                )       
);

comment on table calendars is '
        Table calendars maps the many to many relationship betweens
        calendar and its owners. 
';

comment on column calendars.calendar_id is '
        Primary Key
';

comment on column calendars.calendar_name is '
        the name of the calendar. This would be unique to avoid confusion
';

comment on column calendars.owner_id is '
        the individual or party that owns the calendar
';




-- Ejemplos de Consultas SQL --



select  distinct(calendar_id) as calendar_id,
             calendar_name,
             ' '::varchar as checked_p
    from     calendars
    where    acs_permission__permission_p(calendar_id, :user_id, 'calendar_read') = 't'
    and      acs_permission__permission_p(calendar_id, :user_id, 'calendar_show') = 't'
    and      private_p = 'f'

    
select  on_which_calendar as calendar_id,
            calendar_name,
            ' '::varchar as checked_p
    from    cal_items, calendars
    where   acs_permission__permission_p(cal_item_id, :user_id, 'cal_item_read') = 't'
    and     calendars.private_p = 'f'
    and     cal_items.on_which_calendar = calendars.calendar_id

select   to_char(to_date(:start_date, 'YYYY-MM-DD'), 'D') 
        as       day_of_the_week,
        to_char(next_day(to_date(:start_date, 'YYYY-MM-DD')- '1 week'::interval, :first_us_weekday), 'YYYY-MM-DD')
        as       first_weekday_of_the_week,
        to_char(next_day(to_date(:start_date, 'YYYY-MM-DD'), :last_us_weekday), 'YYYY-MM-DD')
        as       last_weekday_of_the_week
        from     dual

select   to_char(start_date, :ansi_date_format) as ansi_start_date,
         to_char(end_date, :ansi_date_format) as ansi_end_date,
         coalesce(e.name, a.name) as name,
         coalesce(e.status_summary, a.status_summary) as status_summary,
         e.event_id as item_id,
         (to_date(start_date,:ansi_date_format)  - to_date(:first_weekday_of_the_week_tz,
         :ansi_date_format)) as day_of_week,
         cals.calendar_id,
	 cals.calendar_name,
         cit.type as item_type
from     acs_activities a,
         acs_events e,
         timespans s,
         time_intervals t,
         calendars cals,
         cal_items ci left join
         cal_item_types cit on cit.item_type_id = ci.item_type_id
where    e.timespan_id = s.timespan_id
and      s.interval_id = t.interval_id
and      e.activity_id = a.activity_id
and      start_date between
         to_date(:first_weekday_of_the_week_tz, :ansi_date_format) and
         to_date(:last_weekday_of_the_week_tz, :ansi_date_format)
and      cals.calendar_id = ci.on_which_calendar
and      e.event_id = ci.cal_item_id
$calendars_clause
order by to_char(start_date, 'J'), to_char(start_date,'HH24:MI')

select   to_char(start_date, 'YYYY-MM-DD HH24:MI:SS') as ansi_start_date,
             to_char(end_date, 'YYYY-MM-DD HH24:MI:SS') as ansi_end_date,
             to_number(to_char(start_date,'HH24'),'90') as start_hour,
             to_number(to_char(end_date,'HH24'),'90') as end_hour,
             to_number(to_char(end_date,'MI'),'90') as end_minutes,
             coalesce(e.name, a.name) as name,
             coalesce(e.status_summary, a.status_summary) as status_summary,
             e.event_id as item_id,
             cit.type as item_type,
             cals.calendar_id,
             cals.calendar_name
             $additional_select_clause
    from     acs_activities a,
             acs_events e,
             timespans s,
             time_intervals t,
             calendars cals,
             cal_items ci left join
             cal_item_types cit on cit.item_type_id = ci.item_type_id
    where    e.timespan_id = s.timespan_id
    and      s.interval_id = t.interval_id
    and      e.activity_id = a.activity_id
    and      start_date between $interval_limitation_clause
    and      ci.cal_item_id= e.event_id
    and      cals.calendar_id = ci.on_which_calendar
    and      e.event_id = ci.cal_item_id
    $additional_limitations_clause
    $calendars_clause
    $order_by_clause